Solved

SQl query (LIKE Clause)

Posted on 1998-09-23
16
996 Views
Last Modified: 2008-02-01
Hi,( sending the complete query)
 Thanks for all for all the help.
Its really helping me a lot in understanding this due
to all you people's sharing knowledge.
Thanks again.

Rgds,
Bhanu.

I am herewith enclosing the complete query.

SELECT pv.vendor_name,
       pv.segment1,
       ..........
       .........
FROM
   ap_invoices    ai,
   ap_invoice_distributions   aid,
   ap_invoice_payments     aip,
   ap_checks  ac,
   po_vendors  pv,
   ap_batches  ab,
   gl_code_combinations gcc
WHERE
    aip.check_id = ac.check_id
  and ai.invoice_id = aip.invoice_id
  and ai.invoice_id = aid.invoice_id
  and ai.batch_id =   ab.batch_id
  and ai.vendor_id = pv.vendor_id
  and aid.dist_code_combination_id = gcc.code_combination_id
  and gcc.segment1 like '&parameter1%'
  and gcc.segment2 like '&parameter2%'
  and gcc.segment3 like '&parameter3%'
  and ac.check_date between ('01-JAN-98') to ('01-JUL-98')
  and ac.amount > 50000
 GROUP BY
   .....
 .....
  all columns which are in select query and some others too.
 ORDER BY
  ac.check_number,
  pv.vendor_name,
  ai.invoice_num,
  gcc.segment1,
  gcc.segment2,
  gcc.segment3;
 ----Here gl_code_combinations table consists of many records like 2000000.
I am using Oracle 7.3 .(cost based optimiser)

The count of other tables,i will be able to send it a
little later. I guess its ok. I will also try to do explain plan and send the info.

Rgds,
Bhanu.
mail: bhanu_7@hotmail.com

****************************

the below part is earlier part of question
______
Hi,
  This is the problem i am having in this query.Can anyone look into this.

Thanks for the help.

Bhanu.

This is the SQl query which is run with parameters
for col4,col5 and col6.
The user whn running this will give parameters as
Parameter1 = %
parameter2 = %
parameter3 = %
Then the query runs fine (i.e it fetches all the records)

But when the user gives certain values such as
parameter1 = 10000010%
parameter2 = %
parameter3 = %
  now the query runs and runs and thats it. I guess this is due
to LIKE Clause. also the table with these columns is very big . its
around 20000000 records. Index is already present on that column.
that column is not a primary column.

The user should have the option of entering both the ways mentioned above.


   I had of creating a view for that table based on user parameters
and to make use of that view in the query.? But eventhen its running on and on
and not comming out of the query itself.
   Is there any other method of reducing the output time ?
Also i would like to mention that this sqlplus is called from a schell script
to run from concurrent manager in ORACLE APPS.

SELECT  a.col1,b.col2...
        ................
        ................
  FROM  
        table a,
        table b,
        ......
        ......
        ......
  WHERE
        a.col4 like 'parameter1%'
     and a.col5 like 'parameter2%'
     and a.col6 like 'parameter3%'
     and...............
     and .............

  group by
      ...............
      ...............

   Thanks for your help,

Bhanu
mailid: bhanu_7@hotmail.com
0
Comment
Question by:bhanu_7
  • 5
  • 4
  • 3
  • +3
16 Comments
 

Author Comment

by:bhanu_7
ID: 1081692
Thanks for any insight in this regard.

Bhanu.
0
 
LVL 3

Expert Comment

by:vlad_impala
ID: 1081693
It's hard to be exact without knowing the exact query and the exact table definitions and indexes, but .... The LIKE clause should not make much difference to the speed of the query as long as the column is indexed (if it is part of a composite key it must be the first column in the key for it to use the index) (I'm assuming these are VARCHAR2 columns).  So it is probably something else that is causing the problem, it is probably worth puting the query through EXPLAIN PLAN to see how it is accessing the data.

But here are a few things to check....(based on your query above and mistakes I have made in the past!)
1) Make sure you have remembered to join your two tables.
2) Make sure you have optimised your query e.g. make sure that a.col4 is the most restrictive clause and that table a is listed last in the table listing (I would write it for a rule based system even if you are using cost based).
3) Make sure you do not have two conflicting indexes as this can really slow things down, you need to use EXPLAIN PLAN to spot this, or you could just try disabling one of the indexed by using LTRIM or something on it to see if it helps
4) Are any of your tables at the other end of database links? - I've found this can really slow things down.

I suspect your indexes are not being used for some reason, anyway - it may help if you post your query here so we can have a look at it.

Vlad.
0
 
LVL 1

Expert Comment

by:jcasteel
ID: 1081694
As vlad_impela responded, be sure that the col4 is the first column in the index, followed for example with col5 and col6 or some other columns who could restrict the result set.

Can you give some more information about the query because this information is not sufficiant for tuning this statement.
I will try to explain a little bit how Oracle will handle it. If you are using bind variables the SQL-statement in the SGA will probably look like this

SELECT  a.col1,b.col2...
        ................
  FROM  
        table a, table b,
        ......
  WHERE
        a.col4 like :1
     and a.col5 like :2
     and a.col6 like :3
     and...............
  group by
      ...............

where :1, :2 and :3 are the bind variables wich will be substituted at runtime with the correct value. Only the first time when oracle is parsing the query, oracle will determin his access path.
For Oracle
a.col4 like :1
a.col5 like :2
a.col6 like :3
look all the same so he will start reading the where clause from the bottom to the top.... I your case the will start with col6  than col5 and finally col4 , but col6, col5 will return all the records...

One important thing is to put the table wich will return the smallest result set first in the from clause...

But if you give us some more information about your statement , it will easier to give more specific info...
info like the other relations in the where clause and ofcourse a explain plan

Hopes this gives you a first clue

Jan Casteels


0
 
LVL 3

Expert Comment

by:junfeb
ID: 1081695
Here are a few more thoughts -
* Make sure that the database is geared to do a TKPROF/Explain
  How to do this  
  1. Create the plan table in your schema by running
      $ORACLE_HOME/rdbms/admin/utlxplan.sql
  2. If you'd like to have some timing statistics set your init.ora parameter
      Timed_statistics = true - This would mean a bounce of the DB to be made effective
  3. You can use the following script to run explain your sql -
col operation   format  a13     trunc
col options     format  a15     trunc
col object_name format  a23     trunc
col id          format  9999
col parent_id   format  9999
col position    format  9999
col operations  format  a25
 
 explain plan
 set statement_id = 'x'
 into plan_table
 for  
 < Insert your sql statement here >
 
select lpad(' ',2*level) || operation operations,options,object_name
from plan_table
where statement_id = 'x'
connect by prior id = parent_id and statement_id = 'x'
start with id = 1 and statement_id = 'x'
order by id;
 
rollback;

4. if you have oracle 7.3.3 upwards - then
    you can in your sql*plus session
   set autotrace on

Make sure you have run the $ORACLE_HOME/sqlplus /admin/plustrce.sql as sys.

5. You can also get a tkprof output by tracing your session. Explain plan is good for the first round.
6.  Look for full table scans , wrong indexes, joins of tables in the wrong order .
     Vlad_impala, jan were right, You may want to  post your explain plan , and your     table descriptions ,  and don't forget the count on your tables that you hope to retreive  so that we can have a look at it.

This is my thoughts on multi table joins - All of us have got a few of those beasts -
* Make sure that the join colums are in your where clause other wise a cartesian product would result
  select ... from a, b where
  a.x = b.x  - This clause is very important .
* Next are you doing any outer joins - these would cause full table scans
* Are you using cost based / rule base optimiser - Sometimes rule based optimizer   chooses a full table scan - especially if your range search is going to retrieve more than a certain % of your tables  
*  Also depending on the number of rows fetched - an index scan may or may not be a good thing - this also depends on how many levels your index has grown to especially if you have to go to the table to fetch the data.
* The order of tables in your from clause would be important as well .
Once you have the explain plan , I'm sure either you'll figure out your problem area yourself or if you post your plan, details and questions , we can help you.

Thanks.
   

0
 
LVL 1

Expert Comment

by:jcasteel
ID: 1081696
Another comment,

If you use cost based optimizer, be sure that your table has been analyzed recently, if you create a new index for performance anhancement, ... analyze the index because it can give you strange results in some cases....


Greetings


0
 
LVL 3

Expert Comment

by:vlad_impala
ID: 1081697
jcasteel..
I don't want to get into a bun fight about this, but I think you statements about rule based optimisers are ....er, um how do I put this politely?... wrong.

The tables in the FROM statement are parsed right to left so the LAST table in the FROM clause is the first table processed (i.e. the driving table).
In the WHERE clause, in the absense of other information, the parser uses the WHERE clause sequencing to determine the best execution path within the database.  Thus it is better to put the most efficient clauses at the start of the WHERE clause where they are more likely to influence the parser.

Maybe someone else would like to comment on this so we make sure that bhanu_7 gets the right advice.

Vlad.
0
 
LVL 3

Expert Comment

by:junfeb
ID: 1081698
Further Comments on Rankings ..
The tables are parsed from left to right and the where clause is judged according to the rankings ( index rankings) . If two conditions evaluate to the same rank then it is further ranked according to whether they are in the same table. If they are in different tables , then the one that belongs to the driving table is ranked higher, If they belong to the same table, the one that is listed first ranks higher.

The last table in the from clause becomes the driving table, this must typically retrieve least no of rows.

If there are three tables that are joined, the table that has most dependency should be chosen as the driving table.

Be wary of using cost based optimizer. CBO before 7.3, I wouldn't just bother with it.

Thanks.

0
 
LVL 3

Expert Comment

by:junfeb
ID: 1081699
P.S - IMPORTANT

Please read these parts of my  comments as
* Are you using cost based / rule based optimiser - Sometimes 'COST' based  - supposedly intelligent   optimizer   ( NOT RULE - Which just follows a set of rules ) chooses a full table scan - especially if your range search is going to retrieve more than a certain % of your tables .

* Tables are parsed from right to left. I meant to say right to left. Sorry.  My statement about the last table in the from clause is correct. This becomes the driving table. The table that you specify last in the from clause will be processed first by oracle parser.

Thanks.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:bhanu_7
ID: 1081700
Edited text of question
0
 

Author Comment

by:bhanu_7
ID: 1081701
Edited text of question
0
 

Author Comment

by:bhanu_7
ID: 1081702
Thanks again.

Rgds,
Bhanu
0
 
LVL 2

Accepted Solution

by:
avico earned 100 total points
ID: 1081703
Try reruning the query using in a rule based optimizer mode.
Use :
Alter session set optimizer_goal=rule;
to change the mode only for the current session.

Even in 7.3 the cost based optimizer sometimes tend to have a
will on its own...

May I also suggest you to use the sql_trace facility along with the timed_statistics init.ora paramter, rather than the EXPLAIN PLAN. Using sql_trace you can get, in addition to the plan, all
the recursive calls preceeded to yours and how much time each execution took.
0
 

Author Comment

by:bhanu_7
ID: 1081704
Thanks,
I will rerun the query again.

Rgds,
Bharathi.
0
 
LVL 1

Expert Comment

by:jcasteel
ID: 1081705
vlad,

I thought we were talking about cost based optimizing.... otherwise the rules are indeed completely different. If we are talking about rule-base, talking about table analyzes and index analyzes is completely out of the picture...

Personally I do not get it why oracle changed the rules for tuning that hard between the different modes. It makes miggration from rule-based towards cost-based optimizer a much more difficult task.

Cost based optimizer : CBO will use the leftmost table in the from clause as the driving table and will use the left - right order of the tables in the from clause as join order

Rule base : Will take the oppisitte approach of the CBO.

Please tell me if I am wrong but my source is Oracle SQL : High Performance tuning => ISBN 013614231 1 and Oracle Performance tuning O'Reilly  ISBN => 1 56592 2379.

I recommend both books as litterature because they helped me a lot in the tuning of our application ( 1000 concurrent users and 25 GB of data )

Greetings

Jan Casteels



0
 
LVL 1

Expert Comment

by:jcasteel
ID: 1081706
vlad,

I thought we were talking about cost based optimizing.... otherwise the rules are indeed completely different. If we are talking about rule-base, talking about table analyzes and index analyzes is completely out of the picture...

Personally I do not get it why oracle changed the rules for tuning that hard between the different modes. It makes miggration from rule-based towards cost-based optimizer a much more difficult task.

Cost based optimizer : CBO will use the leftmost table in the from clause as the driving table and will use the left - right order of the tables in the from clause as join order

Rule base : Will take the oppisitte approach of the CBO.

Please tell me if I am wrong but my source is Oracle SQL : High Performance tuning => ISBN 013614231 1 and Oracle Performance tuning O'Reilly  ISBN => 1 56592 2379.

I recommend both books as litterature because they helped me a lot in the tuning of our application ( 1000 concurrent users and 25 GB of data )

Greetings

Jan Casteels



0
 
LVL 2

Expert Comment

by:liebla
ID: 1081707
SELECT /*+ ORDERED */ org.c_name, rec.c_name,
FROM Income, consolidation org, consolidation rec, xrates_chf, phierarchy, Income_line
WHERE ....
GROUP BY

/* + ORDERED */ is a hint for the database optimizer to join the tables in the same order as they appear in the FROM part
In the FROM part the largest table (with the most records) should be the first one and the smallest table the last one

e.g. income is the largest table, income_line the smallest

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now