tag:blogger.com,1999:blog-8884584404576003487.post8861243638864735378..comments2024-02-29T09:43:12.251-05:00Comments on ORACLENERD: Analytics in Viewsoraclenerdhttp://www.blogger.com/profile/12412013306950057961noreply@blogger.comBlogger4125tag:blogger.com,1999:blog-8884584404576003487.post-11418158312371085692010-05-04T11:39:10.726-04:002010-05-04T11:39:10.726-04:00Good stuff Chet. Analytics inside views always get...Good stuff Chet. Analytics inside views always gets my head spinning... I can never remember all the rules. It's like the "'i' before 'e' except after 'c'" rule... I always have to fire up a few queries to remind myself.<br /><br />But this one... this was uncharted waters for me. Excellent to know.<br /><br />@Narendra: nice addition.Stewart Brysonhttps://www.blogger.com/profile/16904522901298746643noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-35420096153285378822010-04-28T06:51:12.877-04:002010-04-28T06:51:12.877-04:00Well, it turns out that oracle does rewrite the qu...Well, it turns out that oracle does rewrite the query. A 10053 trace shows following details that confirm the query rewrite.<br />Here is a section in the beginning<br /><b><br />Current SQL statement for this session:<br />select owner, object_name from t_view <br />*******************************************<br /></b><br />and here is the one at the end, which shows the query is rewritten BEFORE the plan is generated.<br /><b><br />******* UNPARSED QUERY IS *******<br />SELECT "T"."OWNER" "OWNER","T"."OBJECT_NAME" "OBJECT_NAME" FROM HR."T" "T"<br />kkoqbc-subheap (delete addr=ffffffff7af1bf90, in-use=11192, alloc=11656)<br />kkoqbc-end<br /> : call(in-use=10232, alloc=32712), compile(in-use=44232, alloc=46360)<br />apadrv-end: call(in-use=10232, alloc=32712), compile(in-use=45016, alloc=46360)<br /> <br />sql_id=aujacrknywd5u.<br />Current SQL statement for this session:<br />select owner, object_name from t_view <br /> <br />============<br />Plan Table<br />============<br />-------------------------------------+-----------------------------------+<br />| Id | Operation | Name | Rows | Bytes | Cost | Time |<br />-------------------------------------+-----------------------------------+<br />| 0 | SELECT STATEMENT | | | | 163 | |<br />| 1 | TABLE ACCESS FULL | T | 50K | 1502K | 163 | 00:00:02 |<br />-------------------------------------+-----------------------------------+<br />Predicate Information:<br />----------------------<br /> <br />Content of other_xml column<br />===========================<br /> db_version : 10.2.0.4<br /> parse_schema : HR<br /> plan_hash : 2153619298<br /> Outline Data:<br /> /*+<br /> BEGIN_OUTLINE_DATA<br /> IGNORE_OPTIM_EMBEDDED_HINTS<br /> OPTIMIZER_FEATURES_ENABLE('10.2.0.4')<br /> ALL_ROWS<br /> OUTLINE_LEAF(@"SEL$F5BB74E1")<br /> MERGE(@"SEL$2")<br /> OUTLINE(@"SEL$1")<br /> OUTLINE(@"SEL$2")<br /> FULL(@"SEL$F5BB74E1" "T"@"SEL$2")<br /> END_OUTLINE_DATA<br /> */<br /></b><br />Hope it helps.Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-88840201164181908102010-04-27T09:37:06.984-04:002010-04-27T09:37:06.984-04:00Narendra,
Good question...I'm not sure if Ora...Narendra,<br /><br />Good question...I'm not sure if Oracle rewrites it or not. Not really sure how to check that either. Maybe someone out there can answer that question...<br /><br />chetoraclenerdhttps://www.blogger.com/profile/12412013306950057961noreply@blogger.comtag:blogger.com,1999:blog-8884584404576003487.post-52804103677476928362010-04-27T04:54:48.721-04:002010-04-27T04:54:48.721-04:00Chet,
Nice one. Learned something new today. So d...Chet,<br /><br />Nice one. Learned something new today. So does it mean oracle actually rewrites the query<br /><b>SELECT QUANTITY_SOLD, AMOUNT_SOLD FROM vw_sales; </b><br />as<br /><b>SELECT QUANTITY_SOLD, AMOUNT_SOLD FROM sales; </b><br />?Narendrahttps://www.blogger.com/profile/14645699853364658640noreply@blogger.com