Article:		Using the New MODEL Clause in Oracle Database 10g
Subject:		10G - not useless.
Date:		2004-08-16 18:20:47
From:		Ol_Dirty_DBA
I am usually not a proponent of vendor specfic features - specifically Oracle's - however, the model clause does look like something that could really be useful. It seems to give some much needed flexibility to sql, although after reading the quite dry oracle documentation on the subject I didn't understand why one would ever need this new feature. The example in this article provides a very interesting use case. Hopefully this will be standard sql in the future, though I have to wonder if this should have been part of the select clause, or its own separate clause? Nice article, and, if you wouldn't mind, can you explain the "10046 trace" a bit?

Showing messages 1 through 1 of 1.

• 10G - not useless.
  2004-08-19 20:14:54  Anthony Molinaro | [View]
  
  
  what i wanted the reader to focus on is here,
  the extra table and index scan:
  
  =====================
  PARSING IN CURSOR #3 len=47 dep=2 uid=0 oct=3 lid=0 tim=11151271963
  hv=1023521005 ad='1a6876ec'
  select metadata from kopm$ where name='DB_FDO'
  STAT #3 id=1 cnt=1 pid=0 pos=1 obj=353 op='TABLE ACCESS BY INDEX ROWID KOPM$
  (cr=2 pr=0 pw=0 time=75 us)'
  STAT #3 id=2 cnt=1 pid=1 pos=1 obj=354 op='INDEX UNIQUE SCAN I_KOPM1 (cr=1 pr=0
  pw=0 time=42 us)'
  =====================
  
  the fact that transformations and casts need to be done in order to generate a table expression from pl/sql to sql. KOPM$ is not specified anywhere in the examples. They are used "under the covers" to produce our "pipelined" results.