Den 2013-08-13 06:52 skrev Dmitry Yemanov såhär:
> All,
>
> There are some kinds of queries that have data access methods linked to
> each other. Windowed functions are the obvious example, but it happens
> even for regular aggregate functions. For example, COUNT(DISTINCT) needs
> not only read the underlying stream but also sort it in order to
> eliminate duplicates. So we have both retrieval and sorting inside the
> aggregation node and sorting is based on the retrieval:
>
> select count(distinct rdb$relation_id)
> from rdb$relations
> group by rdb$relation_name
>
> Select Expression
> -> Aggregate
> -> Table "RDB$RELATIONS" Access By ID
> -> Index "RDB$INDEX_0" Scan
> /*
> -> Sort (unique)
> -> Table "RDB$RELATIONS" Access By ID
> -> Index "RDB$INDEX_0" Scan
> */
>
I fear that I know too little about the inner workings, but I'm a bit
surprised... I would have thought that this is what actually happens:
Select Expression
-> Aggregate
-> Sort (unique)
-> Table "RDB$RELATIONS" Access By ID
-> Index "RDB$INDEX_0" Scan
I.e. scan rdb$index_0 to retrieve data from rdb$relations accessing it
by id:s from the the index, sort the data stream and aggregate the
sorted stream.
If this is not the case I agree that your option E below is most
readable. Another option might be to auto generate some kind of "plan
globally unique" id for the subexpressions that are repeated and refer
to those id:s. Like your option D below, but using some alphanumeric id
instead and have those id:s be unique across the entire plan. Not sure
if it's doable though.
Kjell
> However, I'd like to skip the repeating part and replace it with a
> reference to the earier introduced part, because technically there's
> only one retrieval, not two.
>
> The possible options are:
>
> (A)
>
> Select Expression
> -> Aggregate
> -> Table "RDB$RELATIONS" Access By ID
> -> Index "RDB$INDEX_0" Scan
> -> Sort (unique)
>
> where no input for sorting means the prior stream
>
> (B)
>
> Select Expression
> -> Aggregate
> -> Table "RDB$RELATIONS" Access By ID
> -> Index "RDB$INDEX_0" Scan
> -> Sort (unique)
> -> ^^^
>
> which means to look above for the input stream. But the indentation
> makes it hard to understand what exactly we refer to. And theoretically
> there maybe references to farther parts of the plan, not only the
> neighbours.
>
> (C)
>
> Select Expression
> -> Aggregate
> -> Table "RDB$RELATIONS" Access By ID
> -> Index "RDB$INDEX_0" Scan
> -> Sort (unique)
> -> $base$
>
> some keyword is used to refer to the input stream of the aggregation
>
> (D)
>
> Select Expression
> -> Aggregate
> -> 1: Table "RDB$RELATIONS" Access By ID
> -> Index "RDB$INDEX_0" Scan
> -> 2: Sort (unique)
> -> $(1)
>
> all non-trivial methods have their children enumerated so they can be
> referenced by numbers. But for complex queries there will be multiple
> (1)s and (2)s making it harder to match the proper one.
>
> (E)
>
> Select Expression
> -> 1: Aggregate
> -> 2: Table "RDB$RELATIONS" Access By ID
> -> 3: Index "RDB$INDEX_0" Scan
> -> 4: Sort (unique)
> -> $(2)
>
> or
>
> Select Expression
> 1: -> Aggregate
> 2: -> Table "RDB$RELATIONS" Access By ID
> 3: -> Index "RDB$INDEX_0" Scan
> 4: -> Sort (unique)
> 5: -> $(2)
>
> all methods are globally enumerated. The output is a bit noisy, but
> personally I favor (E)#2 so far.
>
> Comments anyone? Other suggestions?
>
>
> Dmitry
>
> ------------------------------------------------------------------------------
> Get 100% visibility into Java/.NET code with AppDynamics Lite!
> It's a free troubleshooting tool designed for production.
> Get down to code-level detail for bottlenecks, with <2% overhead.
> Download for free and get started troubleshooting in minutes.
> http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
> .
>
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kj...@datadia.se
Telefon: 08-761 06 55
Mobil: 0733-44 24 64
------------------------------------------------------------------------------
Get 100% visibility into Java/.NET code with AppDynamics Lite!
It's a free troubleshooting tool designed for production.
Get down to code-level detail for bottlenecks, with <2% overhead.
Download for free and get started troubleshooting in minutes.
http://pubads.g.doubleclick.net/gampad/clk?id=48897031&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
