Re: Using bind variables for multiple values

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 26 Nov 2008 01:51:46 -0800 (PST)
Message-ID: <4a7b2e28-e10d-4995-917b-6e79e652c5cb@j32g2000yqn.googlegroups.com>

On Nov 26, 7:34 am, Shakespeare <what..._at_xs4all.nl> wrote:
> Robert Klemme schreef:
> > Interesting.  Incidentally there is a recent blog post:
>
> >http://jonathanlewis.wordpress.com/2008/11/11/first_rows_n/
>
> Thanks for the link! I wasn't advertising on using first rows,

I just thought the link fits the context. :-)

> but was
> trying to show that the plans depend on statistics and optimizer settings.

Point taken.

> By the way: I noticed in a test database that all selects on constructs
> of table(iset(1,2,3)) boil down to the optimizer guessing 8168 rows...
> no matter what type is used or how many rows/blocks.

Basically I see the same. There is only this anomaly (?) that for "... WHERE id in ( select column_value from table(iset(-1,2,3)) )" it assumes cardinality 255 for table(iset(-1,2,3)):

Join order[2]: KOKBF$[KOKBF$]#1 T1[T1]#0

    SORT resource      Sort statistics
      Sort width:         349 Area size:      307200 Max Area size:
61446144
      Degree:               1
      Blocks to Sort:      13 Row size:           13 Total
Rows:           8168
      Initial runs:         1 Merge passes:        0 IO Cost /
pass:          0
      Total IO sort cost: 0      Total CPU sort cost: 9098366
      Total Temp space used: 0

---

Now joining: T1[T1]#0

---

NL Join
  Outer table: Card: 255.25 Cost: 17.56 Resp: 17.56 Degree: 1 Bytes: 2
  Inner table: T1 Alias: T1
  Access Path: TableScan
    NL Join: Cost: 513378.14 Resp: 513378.14 Degree: 1

      Cost_io: 497366.00  Cost_cpu: 69106002705
      Resp_io: 497366.00  Resp_cpu: 69106002705
  Access Path: index (UniqueScan)

    Index: SYS_C007309
    resc_io: 2.00 resc_cpu: 16313
    ix_sel: 1.0000e-06 ix_sel_with_filters: 1.0000e-06

    NL Join: Cost: 528.52  Resp: 528.52  Degree: 1
      Cost_io: 525.00  Cost_cpu: 15195495
      Resp_io: 525.00  Resp_cpu: 15195495

  Access Path: index (AllEqUnique)
    Index: SYS_C007309
    resc_io: 2.00 resc_cpu: 16313
    ix_sel: 1.0000e-06 ix_sel_with_filters: 1.0000e-06

    NL Join: Cost: 528.52  Resp: 528.52  Degree: 1
      Cost_io: 525.00  Cost_cpu: 15195495
      Resp_io: 525.00  Resp_cpu: 15195495
  Best NL cost: 528.52
          resc: 528.52 resc_io: 525.00 resc_cpu: 15195495
          resp: 528.52 resp_io: 525.00 resp_cpu: 15195495

Join Card: 255.25 = outer (255.25) * inner (1000000.00) * sel (1.0000e-06)
Join Card - Rounded: 255 Computed: 255.25

Since I still have no clear idea where this comes from I am a bit wary to use this. Who guarantees that the plan won't degrade?

Kind regards

robert Received on Wed Nov 26 2008 - 03:51:46 CST