Pradeep_Tiwari
asked on
select query performance
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.el ementID
And A.Ticker=C.Ticker And A.Period=C.Period And A.elementPropertyName=C.La belId
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/Y YYY')||'-' ||TO_CHAR( enddate,'M M/DD/YYYY' )='12/31/2 006-12/31/ 2006'
Query is like:
SELECT a.elementid,D.ELEMENTORDER
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.el
And A.Ticker=C.Ticker And A.Period=C.Period And A.elementPropertyName=C.La
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/Y
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.
ASKER
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:
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:
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).
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).
ASKER
startdate and enddate belongs to table XBrlTickerDetail.
give proper solution for this problem.
i am not able to send explain plan.
give proper solution for this problem.
i am not able to send explain plan.
Hi,
You might try this one (it''s a bit hard if I don't know the current approach of the database):
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'
ASKER
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:
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:
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.