?
Solved

select query performance

Posted on 2008-10-29
9
Medium Priority
?
596 Views
Last Modified: 2013-12-07
i am using oracle 10gR2 database, when i am going to execute one query my database take much more time for output result.How to increase performane of these queries.
Query is like:
SELECT a.elementid,D.ELEMENTORDER,LabelText AS ElementLabel  ,Amount as Amount0 ,0 as Amount1 ,0 as Amount2 ,
0 as Amount3 ,0 as Amount4 ,0 as Amount5
FROM XBrlTickerDetail  A, XBrlTmpXsdDet  B, XBrlTickerLab  C ,XBRLTICKERPRE  D
WHERE A.ticker=b.ticker And a.period=b.period And a.elementPropertyName=b.elementID
And A.Ticker=C.Ticker And A.Period=C.Period And  A.elementPropertyName=C.LabelId
And A.role='http://xbrl.us/us-gaap/role/statement/StatementOfShareholdersEquityAndOtherComprehensiveIncome'
And A.ticker='edgr' and A.period='20071231'
AND A.TICKER=D.TICKER AND A.PERIOD=D.PERIOD AND A.ROLE=D.ROLE AND A.ELEMENTID=D.ELEMENTID
and TO_CHAR(startdate,'MM/DD/YYYY')||'-'||TO_CHAR(enddate,'MM/DD/YYYY')='12/31/2006-12/31/2006'
0
Comment
Question by:Pradeep_Tiwari
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
9 Comments
 
LVL 9

Expert Comment

by:MarkusId
ID: 22829289
As it depends on a lot of factors, it would be nice to know, how many records are in each of these tables and which indexes there are. Furthermore, could you please post an explain-plan? That would make it much easier.
0
 

Author Comment

by:Pradeep_Tiwari
ID: 22829915
Each tables have 5000 to 10000 records.
indexes are:
table: XBrlTickerDetail: Index:ROLE, PERIOD, ELEMENTID, TICKER
table:XBrlTmpXsdDet: Index:PERIOD, ELEMENTID, TICKER
table:XBrlTickerLab: Index: TICKER, LABELID, PERIOD
table:XBRLTICKERPRE: Index:PERIOD, ELEMENTID, ROLE, TICKER
Explain Plan:
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22830239
I'm afraid, the explain plan is missing in your last post.

Which table do startdate and enddate belong to?
I would use  XBrlTickerDetail as the driving table (so it should be the last table in the from-clause), although the optimizer probably does use it this way anyway (as long as the table statistics have been gathered not too long ago).
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

by:Pradeep_Tiwari
ID: 22838517
startdate and enddate belongs to table XBrlTickerDetail.
give proper solution for this problem.
i am not able to send explain plan.
0
 
LVL 9

Expert Comment

by:MarkusId
ID: 22838692
Hi,

You might try this one (it''s a bit hard if I don't know the current approach of the database):


SELECT a.elementid, D.ELEMENTORDER, LabelText AS ElementLabel,
       Amount as Amount0, 0 as Amount1, 0 as Amount2,
       0 as Amount3, 0 as Amount4, 0 as Amount5
  FROM XBrlTmpXsdDet  B, XBrlTickerLab  C, XBRLTICKERPRE  D,
       XBrlTickerDetail  A
 WHERE B.ticker = A.ticker
   AND B.period = A.period
   AND B.elementid = A.labelid
   AND C.period = A.period
   AND C.labelid = A.elementPropertyName
   AND C.ticker = A.ticker
   AND D.ticker = A.ticker
   AND D.role = A.role
   AND D.elementid = A.elementid
   AND D.period = A.period
   AND trunc(a.startdate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND trunc(a.enddate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND A.ticker = 'edgr'
   AND A.period = '20071231'
   AND A.role = 
       'http://xbrl.us/us-gaap/role/statement/StatementOfShareholdersEquityAndOtherComprehensiveIncome'

Open in new window

0
 

Author Comment

by:Pradeep_Tiwari
ID: 22839452
It's showing Errors:

Error starting at line 1 in command:
SELECT a.elementid, D.ELEMENTORDER, LabelText AS ElementLabel,
       Amount as Amount0, 0 as Amount1, 0 as Amount2,
       0 as Amount3, 0 as Amount4, 0 as Amount5
  FROM XBrlTmpXsdDet  B, XBrlTickerLab  C, XBRLTICKERPRE  D,
       XBrlTickerDetail  A
 WHERE B.ticker = A.ticker
   AND B.period = A.period
   AND B.elementid = A.labelid
   AND C.period = A.period
   AND C.labelid = A.elementPropertyName
   AND C.ticker = A.ticker
   AND D.ticker = A.ticker
   AND D.role = A.role
   AND D.elementid = A.elementid
   AND D.period = A.period
   AND trunc(a.startdate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND trunc(a.enddate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND A.ticker = 'edgr'
   AND A.period = '20071231'
   AND A.role = 'http://xbrl.us/us-gaap/role/statement/StatementOfShareholdersEquityAndOtherComprehensiveIncome'
 
Error at Command Line:8 Column:21
Error report:
SQL Error: ORA-00904: "A"."LABELID": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
0
 
LVL 9

Accepted Solution

by:
MarkusId earned 2000 total points
ID: 22839466
Oh, sorry, it should have been B.Elementid = A.elementPropertyName in line 8:
SELECT a.elementid, D.ELEMENTORDER, LabelText AS ElementLabel,
       Amount as Amount0, 0 as Amount1, 0 as Amount2,
       0 as Amount3, 0 as Amount4, 0 as Amount5
  FROM XBrlTmpXsdDet  B, XBrlTickerLab  C, XBRLTICKERPRE  D,
       XBrlTickerDetail  A
 WHERE B.ticker = A.ticker
   AND B.period = A.period
   AND B.elementid = A.elementPropertyName
   AND C.period = A.period
   AND C.labelid = A.elementPropertyName
   AND C.ticker = A.ticker
   AND D.ticker = A.ticker
   AND D.role = A.role
   AND D.elementid = A.elementid
   AND D.period = A.period
   AND trunc(a.startdate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND trunc(a.enddate) = to_date('12/31/2006', 'MM/DD/YYYY')
   AND A.ticker = 'edgr'
   AND A.period = '20071231'
   AND A.role = 
       'http://xbrl.us/us-gaap/role/statement/StatementOfShareholdersEquityAndOtherComprehensiveIncome'

Open in new window

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 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