• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 569
  • Last Modified:

Crystal SQL Command report using Oracle 10g - how to query using indexes

Hello,

I'm writing a report off an Oracle table called purchorder which has more 1 million + rows of data.  I want to write an efficient SQL command with SQL command parameters and pass dates into the SQL command parameters.  One of the indexes on the oracle tables uses date fields.  Any ideas on how to construct the query?
0
jrbledsoe001
Asked:
jrbledsoe001
  • 8
  • 6
  • 2
  • +1
4 Solutions
 
mrjoltcolaCommented:
1) Examine the indexes and find out the columns involved.

2) Understanding what "leading columns" means, and always use leading columns in your query to ensure indexes are used. ie if the index is on (col1, col2, col3) but your query leaves out col1, then col3 won't be used to access the index. So always include leading columns.

3) For a date index, you can use "WHERE dateCol BETWEEN <start date> AND <stop date> AND ..." to allow a range scan to happen. Range scans are preferable to full table scans. If you want an exact match on a date field, that is a different story, and will require more details.
0
 
slightwv (䄆 Netminder) Commented:
Not a Crystal person so cannot help with the specifics.  

From an Oracle perspective, make sure the table date columns don't end up with any functions on them and you should be OK.
0
 
jrbledsoe001Author Commented:
The index is called PRCSET7 and the fields are COMPANY, PO-DATE, PO-NUMBER, PO-CODE and PO-RELEASE.  My index values will be company >=10 and company <=9999, po-date >= to_date('01/01/2011', 'mm/dd/yyyy') and po-date <= to_date('01/31/2011', 'mm/dd/yyyy') and po-number >=1 and po-number <=999999999999 and so forth.  I'll pass in SQL command variables for the hard coded dates in this example once the sql statement is running efficiently.
 
Please suggest if there is a better way to fetch data using dates from the oracle table


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mlmccCommented:
Are you trying to do this in the Crystal command or as an Oracle view or SP?

mlmcc
0
 
slightwv (䄆 Netminder) Commented:
Based on what you posted, it may or may not use an index.  It's possible that using the index would actually slow things down.   Long gone are the days when indexes=good and no index=bad.

Oracle uses a Cost Based Optimizer to figure out how to execute and given SQL (DDL excluded).  It does this based on object statistics.  It can generate a plan and tell you what it is going to do.

Do you have access to sqlplus (the Oracle command line SQL program)?

If you have access to sqlplus (or a related tool), please post the results of:

explain plan for
select ... -- the rest of your select statement.

If that is successful please post the results from:  
SELECT * FROM TABLE(dbms_xplan.display);
0
 
jrbledsoe001Author Commented:
mlmccs asked Are you trying to do this in the Crystal command or as an Oracle view or SP?

I am using the sql statement in the Crystal command.
0
 
mlmccCommented:
So long  as you make your parameters as dates it should be ok

po-date >= {?StartDate} AND po-date <= {?EndDate}

mlmcc
0
 
slightwv (䄆 Netminder) Commented:
To verify, capture the generated sql and look at the plan.  Make sure database statistics are up to date.
0
 
jrbledsoe001Author Commented:
Thanks everyone for providing so much input on my question!!  I'm response to slightwv, I am very new to PL Sql but I have used other SQL tools in the past.  The purchorder table is so large I can not get a query to run on a date range without it taking all day.  I'm trying to find a smaller subset of data so that the query will actually run in a timely manner.  Additionally I'm working on an old test server which is underpowered as well.  Thanks for telling me about explain plan - I'll research that and try to post the results you requested.  
0
 
jrbledsoe001Author Commented:
In response to mlmcc and mrjoltcola, your input has caused me to question my approach.  I am exploring incorporating the date index in my crystal sql command query because I thought the statement would run more efficiently on the table.  I thought that the date index would scan the table faster since I would be selecting records on dates which are part of the table index.  Your posts have helped me understand that I many not need to use the date index and may achieve the same results by using a date scan and not referencing the PRCSET7 index at all.  Any wisdom you can provide will be most appreciated.  Thanks again for posting your input to my question so quickly!!!
0
 
jrbledsoe001Author Commented:
slightwv,  the information on explain plan is so helpful.  i found a smaller subset of data so that my query is actually completing.  i'll post the results once i have the query running with the date ranges  -  thanks again!!!
0
 
jrbledsoe001Author Commented:
In response to mrjoltcola..thanks for reminding me about leading columns.  Your input is already helping me write a more efficient sql command statement.  I'll post more once I get the sql statement together with all the desired columns.

0
 
mrjoltcolaCommented:
Just to clarify my statement about leading columns. You only need to include them in the join or predicate (where clause). The columns don't have to be in the SELECT clause.
0
 
slightwv (䄆 Netminder) Commented:
Yes, the explain plan is the #1 thing to look at when starting a tuning session when you are sure the table/object statistics are current.

Full Table Scans (FTS) are the first thing to look at.  You need to determine if they are OK and if you can eliminate them (some times they are actually the best approach).

Once you think you have the SQL as good as you can get it, then you can start looking at other options.

I'll wait for the plan...
0
 
jrbledsoe001Author Commented:
Hi slightwv,  

I modified my sql so that I would only pull in 100+ records (company 30).  Here are the results - it looks like I don't type sql code to reference the index with the date range since Oracle automatically uses an index which contains the date.  Oracle automatically picked index PCRSET3 COMPANY, BUYER-CODE, PO-DATE based on my crieteria.  

Explain plan for
select
pcr.company,
pcr.po_code,
pcr.po_number,
pcr.po_release,
pcr.po_date,
pcr.buyer_code
from lawprod.purchorder pcr
where pcr.company = 30
and pcr.po_code ='    '
and pcr.po_date >= to_date('01/01/2001','mm/dd/yyyy') and pcr.po_date <= to_date('01/31/2011','mm/dd/yyyy');


SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1204446361

----------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |         |   119 |  4403 |     3   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| PCRSET3 |   119 |  4403 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - access("PCR"."COMPANY"=30 AND
              SYS_OP_DESCEND("PO_DATE")>=HEXTORAW('8790FEE0FEF8FEFAFF')  AND
              "PCR"."PO_CODE"='    ' AND SYS_OP_DESCEND("PO_DATE")<=HEXTORAW('87
9AFEF8

              FEF8FEFAFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("PO_DATE"))>=TO_DATE('
              2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "PCR"."PO_CODE
"='


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
               ' AND SYS_OP_UNDESCEND(SYS_OP_DESCEND("PO_DATE"))<=TO_DATE('

              2011-01-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
             
20 rows selected.
0
 
slightwv (䄆 Netminder) Commented:
The Oracle CBO will try and pick the most efficient path to retrieve the data you are asking for.  Most of the time it does this correctly.

Nothing looks all that bad in the plan you posted.  I'm concerned by the statement 'you modifired'.  Does it choose the same plan for the larger amount of data?

You don't actually have to execute the query to generate the plan.  Back in http:#a35495310 I mentioned there is an 'explain plan for' call you can make from sqlplus.

This doesn't execute the code, it just generates the execution plan.
0
 
jrbledsoe001Author Commented:
Hi slightvw,  

With your help I have an efficient running query and now I know how to use the explain plan to view in advance how my query will run.  The query is pulling in the records on a large subset of data in just a few moments!!  Since I'm new to SQL Plus I'm going to train myself to use the explain plan whenever I venture out into my huge tables.  Thanks for all your help.  This knowledge is going to save me a ton of time!!  
0
 
slightwv (䄆 Netminder) Commented:
I hope I speak for everyone here:  Glad to help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now