Home > Default > Query takes longer to run with indexes.

Query takes longer to run with indexes.

November 30Hits:0
Advertisement
Here is my situation. I had a query which I use to run in Production (oracle 9.2.0.5) and Reporting database (9.2.0.3). The time taken to run in both databases was almost the same until 2 months ago which was about 2 minutes. Now in production the query does not run at all where as in Reporting it continues to run in about 2 minutes. Some of the things I obsevred in P are 1) the optimizer_index_cost_adj parameter was changed to 20 from 100 in order to improve the performance of a paycalc program about 3 months ago. Even with this parameter being set to 20, the query use to run in 2 minutes until 2 months ago. in the last two months the GL table increased in size from 25 million rows to 27 million rows. With optimizer_index_cost_adj of 20 and Gl table of 25 million rows it runs fine, but with 27 million rows it does not run at all. If I change the value of optimizer_index_cost_adj to 100 then the query runs with 27 million rows in 2 minutes and I found that it uses full table scan. In Reporting database it always used full table sacn as found thru explain plan. CBO determines which scan is best and it uses that. So my question is that by making optimizer_index_cost_adj = 20, does oracle forces it to use index scan when the table size is 27 million rows? Isn't the index scan is not faster than full table scan? In what situation the full table scan is faster than index scan? If I drop all the indexes on the GL table then the query runs faster in production as it uses full table scan. What is the real benefit of changing optimizer_index_cost_adj values? Any input is most welcome.

Answers

Isn't the index scan is not faster than full table scan? In what situation the full table scan is faster than index scan? No. It is not about which one is the "+fastest+" as that concept is flawed. How can an index be "faster" than a table for example? Does it have better tires and shinier paint job? ;-)
It is about the amount of I/O that the database needs to perform in order to use that object's contents for resolving/executing that applicable SQL statement.
If the CBO determines that it needs a 100 widgets worth of I/O to scan the index, and then another 100 widgets of I/O to scan the table, it may decide to not use the index at all, as a full table scan will cost only a 180 I/O widgets - 20 less than the combined scanning of index and table.
Also, a full scan can make use of multi-block reads - and this, on most storage/file systems, is faster than single block reads.
So no - a full table scan is NOT a Bad Thing (tm) and not an indicator of a problem. The thing that is of concern is the amount of I/O. The more I/O, the slower the operation. So obviously, we want to make sure that we design SQL that requires the minimal amount of I/O, design a database that support minimal I/O to find the required data (using clusters/partitions/IOTs/indexes/etc), and then check that the CBO also follows suit (which can be the complex bit).
But before questioning the CBO, first question your code and design - and whether or not they provide the optimal (smallest) I/O footprint for the job at hand.

Read other 4 answers

Tags:

Related Articles

  • Query takes longer to run with indexes.November 30

    Here is my situation. I had a query which I use to run in Production (oracle 9.2.0.5) and Reporting database (9.2.0.3). The time taken to run in both databases was almost the same until 2 months ago which was about 2 minutes. Now in production the qu

  • Query tuning and using the "better" index.November 30

    I have a database table with about 40,000 records in it. Using a certain index first limits the number of rows to 11,000 records. Using a different index first (by disabling the other index in the query) limits the number of rows to 2,500 records. Us

  • Query Performance - Query very slow to runNovember 30

    I have built a query to show payroll costings per month per employee by cost centres for the current fiscal year. The cost centres are selected with a hierarchy variable - it's quite a latrge hierarchy. The problem is the query takes ages to run - ne

  • Query on a table runs more than 45mins(after stats) and same query runs 19secs(before stats - rebuild)November 30

    Query on a table runs more than 45mins(after stats) and same query runs 19secs(before stats - rebuild) - Not sure what the cause is. - Analysed the explain the plan - different explain plan used afterr stats gather. Any idea what could be the cause w

  • Recommended frequency for running Rebuild Indexes Maintenance task on CASNovember 30

    How frequently shall we run "Rebuild Index" maintenance task on CAS?You should also consider using an actual SQL index rebuild script instead of the built-in maintenance task as described at http://stevethompsonmvp.wordpress.com/2013/05/07/optim

  • How to get query execution time without running...?November 30

    Hi , I had one requirement .... as follows ...... i had 3 sql statements . I need to execute only one sql which execution time is very less. Can any one help me , how to get query execution time without running that query and without using explain pl

  • Query slows down after second run for Index Organised TablesNovember 30

    We are trying to optimise our application which supports MSSQL to run with Oracle 9i for one of our customers. We have created one database with normal tables and PK constraints/indexes and turned caching on for the tables, this seems to work well bu

  • Same query has vastly different run times on different DBsOctober 11

    I have a query that is run on two different Oracle DBs (located on separate computers). One finishes in about 45 minutes; the other takes over two hours (how much longer, I can't tell you, as Linux keeps dropping the connection at the two-hour mark,

  • Query with trim function running more than 15 hours..November 30

    Hi, I have one select query which is running more than 15 hours.I have many trim() functions in that.and i have index for some columns for the tables that i used in that query.but it is not considering those indexes.. select DISTINCT trim(A.x) x, tri

  • Simple query takes time to runNovember 30

    Hi, I have a simple query whcih takes about 20 mins to run.. here is the TKPROF forit:   SELECT     SY2.QBAC0,     sum(decode(SALES_ORDER.SDCRCD,'USD', SALES_ORDER.SDAEXP,'CAD', SALES_ORDER.SDAEXP /1.0452))   FROM     JDE.F5542SY2  SY2,     JDE.F4211

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.568 s.