Home » SQL & PL/SQL » SQL & PL/SQL » Another query tuning problem
Another query tuning problem [message #194552] Fri, 22 September 2006 11:02 Go to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
I have a very simple query...

select * from env_order_header order by order_rush


that executes in 19 seconds which is unworkable. The table has 73,000 records and about 9,000 of those records have the value "RUSH" in the order_rush field - all of the other rows have NULL for that field. I've tried putting an index on that field but it didn't help. Why is it so slow? When I run EXPLAIN PLAN we get this...

SELECT STATEMENT
SORT ORDER BY
TABLE ACCESS FULL

I'm using Oracle 9i.
Re: Another query tuning problem [message #194553 is a reply to message #194552] Fri, 22 September 2006 11:05 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
That access path is what is to be expected for a query that returns every column in the table and has no WHERE clause.
Re: Another query tuning problem [message #194555 is a reply to message #194552] Fri, 22 September 2006 11:08 Go to previous messageGo to next message
ferrethouse
Messages: 43
Registered: August 2006
Member
Without the order clause it executes in 0.3 seconds. I thought the purpose of an index was to speed sorting. Is this a deficiency unique to Oracle? The first row in the EXPLAIN PLAN is sort - why doesn't it use the index to sort and then return all the rows?

[Updated on: Fri, 22 September 2006 11:08]

Report message to a moderator

Re: Another query tuning problem [message #194570 is a reply to message #194555] Fri, 22 September 2006 13:22 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Oracle will use an index to locate rows. An index would only potentially help with sorting if you were returning only the column(s) in the index.

In your case, every row has to be visited given your * select, so using an index (and then a rowid lookup) would make performance even worse.
Re: Another query tuning problem [message #194571 is a reply to message #194555] Fri, 22 September 2006 13:40 Go to previous messageGo to next message
jrich
Messages: 35
Registered: February 2006
Member
It doesn't use the index because most likely it will be way more expensive to read the table in sorted order (ie by walking the index), then to read the whole table via a full table scan and then sorting. Unless the cluster factor on the index on order_rush was very good, walking the index and reading table blocks would result in multiple reads of each block.

But a bigger problem is that completely null values for an index entry are not stored in the index. So an index on the column order_rush would only contain entries for nonnull values, so it could not be used for sorting all entries.

I would work on the sorting part of this query. Depending on your oracle version, connection method (dedicated vs shared server), and init.ora parameters, you may need to tune your automatic or manual pga parameters, ie give more in-memory sort space to your process.

Run the query from sqlplus after turning autotrace on (SET AUTOTRACE ON) and see how many sorts are done to memory vs disk. If any sorts are being done to disk, then I would try raising the size of your PGA.

JR



Re: Another query tuning problem [message #194610 is a reply to message #194571] Sat, 23 September 2006 00:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you just want the query to START returning rows quickly (ie. fastest performance for the first rows to be returned, rather than all of the rows).

  • Create a function based index on NVL(order_rush, chr(1))
  • Rewrite the query
    select * 
    from env_order_header 
    where nvl(order_rush, chr(1)) >= chr(1)
    order by nvl(order_rush, chr(1))


FYI. Indexes are rarely used to speed up sorting. The ONLY case where it occurs (which I exploited above) is where there is a RANGE SCAN on an index (WHERE clause), no other WHERE clause, and the ORDER BY lists a leading subset of the columns in the index.

Even so, this code will probably be slower than a FTS for returning the entire table.

Ross Leishman
Re: Another query tuning problem [message #194832 is a reply to message #194610] Mon, 25 September 2006 11:18 Go to previous message
ferrethouse
Messages: 43
Registered: August 2006
Member
Thanks guys. That really helps me understand things. I guess doing a select * and a sort on a table with 35 fields is a bad idea. If I explicity specify a limited number of fields the query is quite fast.
Previous Topic: standardize time series
Next Topic: change my constraint
Goto Forum:
  


Current Time: Fri Dec 02 20:57:32 CST 2016

Total time taken to generate the page: 0.14004 seconds