ansaurus

Question

Performance tuning about the "ORDER BY" and "LIKE" clause

Answer 1

A:

Create indexes on the stringField and dateField columns. The SQL engine uses them automatically.

2010-09-24 07:14:06

I have already create an index on TableA.stringField and TableA.dateField ,but it is very slow .

2010-09-24 07:19:39

It is better to collect new statisctics after you did a ddl statement like create table, alter table or create index.

TTT 2010-09-25 09:10:42

Answer 2

A:

How many records does Table A has if it's the smaller table could you do the select on that table and then loop though the results retrieving the Table B records, as both the select and the sort are on TableA.

A good experiment would be to remove the join and test the speed on that also if allowed can you put the rownum < 201 as an AND clause on the main query. It's probable at the moment that the query is returning all rows to the outer query and then it's getting trimmed?

Jane T 2010-09-24 07:29:21

Answer 3

A:

select id from(   
   select /*+ INDEX(TableB stringField_indx)*/ TableB.id from 
        TableA join TableB on TableA.id = TableB.id
        where  TableA.stringField like 'vr2_input' || '%'
        order by  TableA.dateField desc   
) where rownum < 201

next:

SELECT * FROM TableB WHERE id iN( id from first query)

Please send stats and DDL of this tables.

iddqd 2010-09-24 07:49:05

Answer 4

A:

If you have enough memory you can hint the query to use hash join. Could you please attach the explain plan

Zoltan Hamori 2010-09-24 07:50:59

Answer 5

+1 A:

Do you need to select all columns (*)? The optimizer will be more likely to full scan if you select all columns. If you need all columns in output you may be better to select the id in your inline view and then join back to select other columns, which could be done with an index lookup. Try running an explain plan for both cases to see what the optimizer is doing.

Lord Peter 2010-09-24 07:53:33

Answer 6

+2 A:

You say it's taking about 4 minutes to run the query. The EXPLAIN PLAN output shows an estimate of 18 seconds. So the optimizer is probably far off on some of its estimates in this case. (It could still be choosing the best possible plan, but maybe not.)

The first step in a case like this is to get the actual execution plan and statistics. Run your query with the hint /*+ gather_plan_statistics */, then immediately afterwards execute select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')).

This will show the actual execution plan that was run, and for each step it will show the estimated rows, actual rows, and actual time taken. Post the output here and maybe we can say something more meaningful about your issue.

Without that information, my suggestion is to try out the following rewrite of the query. I believe it is equivalent since it appears that ID is the primary key of TableB.

select TableA.id,
       TableA.stringField,
       TableA.dateField,
       TableA.someField2,
       TableA.someField3,
       TableB.someField1,
       TableB.someField2,
       TableB.someField3,
  from (select * from(
         select   
                  TableA.id,
                    TableA.stringField,
                    TableA.dateField,
                    TableA.someField2,
                    TableA.someField3,
                    from TableA 
                    WHERE TableA.stringField  like '21'||'%'  
                 order by TableA.dateField  desc
          )
          where rownum < 201
       ) TableA
       join TableB  on  TableA.id=TableB.id

Dave Costa 2010-09-24 18:35:12

+1, gather_plan_statistics is so cool. I can't believe I hadn't heard of it before.

jonearles 2010-09-25 03:26:51

Answer 7

A:

You can create one function index on tableA. That will return 1 or 0 based on the condition TableA.stringField like 'vr2_input' || '%' is satisfied or not. That index will make query run faster. The logic of the funtion will be

if (substr(TableA.stringField, 1, 9) = 'vr2_input' THEN return 1; else return 0;

Using actual column names instead of "*" may help. At least common column names should be removed.

Vishnu Gupta 2010-09-25 14:15:32

ansaurus

tags:

views:

answers:

Performance tuning about the "ORDER BY" and "LIKE" clause

related questions