SEO

Hi All
Need top repeat a select 3 times but with differents where condition.
I tried to repeat the selects and after use a couple of union to join
then, but it takes toooo long.

I also remember there was a way such as:

using var (select stamment)
select *
from var
where .....

but i can not remember the exact syntaxes.

Does anybody know?.

Thanks.

On Jul 5, 5:50 pm, valigula <valig...@gmail.com> wrote:
> Hi All
> Need top repeat a select 3 times but with differents where condition.
> I tried to repeat the selects and after use a couple of union to join
> then, but it takes toooo long.
>
> I also remember there was a way such as:
>
> using var (select stamment)
> select *
> from var
> where .....
>
> but i can not remember the exact syntaxes.
>
> Does anybody know?.
>
> Thanks.

'syntaxes' are looked up in the SQL reference manual, NOT by posting
here!!
and it is the WITH statement

--
Sybrand Bakker
Senior Oracle DBA

On 5 jul, 18:11, sybrandb <sybra...@gmail.com> wrote:
> On Jul 5, 5:50 pm, valigula <valig...@gmail.com> wrote:
>
>
>
>
>
> > Hi All
> > Need top repeat a select 3 times but with differents where condition.
> > I tried to repeat the selects and after use a couple of union to join
> > then, but it takes toooo long.
>
> > I also remember there was a way such as:
>
> > using var (select stamment)
> > select *
> > from var
> > where .....
>
> > but i can not remember the exact syntaxes.
>
> > Does anybody know?.
>
> > Thanks.
>
> 'syntaxes' are looked up in the SQL reference manual, NOT by posting
> here!!
> and it is the WITH statement
>
> --
> Sybrand Bakker
> Senior Oracle DBA- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

Thanks ..
Already looked in the book ... but was not sure what to look for ..

> > On Jul 5, 5:50 pm, valigula <valig...@gmail.com> wrote:
>
> > > Hi All
> > > Need top repeat a select 3 times but with differents where condition.
> > > I tried to repeat the selects and after use a couple of union to join
> > > then, but it takes toooo long.

Instead of UNION you could use OR in your WHERE clause.

Select * from sometable where name = 'JOHN'
union
Select * from sometable where salary > 500
union
Select * from sometable where state <> 'NY';

Select * from sometable where name = 'JOHN' OR salary > 500 OR state
<> 'NY';

If your queries take too long, try and index the tables. The index
fields must match your WHERE fields.

Chris L. wrote:
>>> On Jul 5, 5:50 pm, valigula <valig...@gmail.com> wrote:
>>>> Hi All
>>>> Need top repeat a select 3 times but with differents where condition.
>>>> I tried to repeat the selects and after use a couple of union to join
>>>> then, but it takes toooo long.
>
> Instead of UNION you could use OR in your WHERE clause.
>
> Select * from sometable where name = 'JOHN'
> union
> Select * from sometable where salary > 500
> union
> Select * from sometable where state <> 'NY';
>
> Select * from sometable where name = 'JOHN' OR salary > 500 OR state
> <> 'NY';
>
> If your queries take too long, try and index the tables. The index
> fields must match your WHERE fields.

Very inefficient compared with WITH.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On 5 jul, 19:50, DA Morgan <damor...@psoug.org> wrote:
> Chris L. wrote:
> >>> On Jul 5, 5:50 pm, valigula <valig...@gmail.com> wrote:
> >>>> Hi All
> >>>> Need top repeat a select 3 times but with differents where condition.
> >>>> I tried to repeat the selects and after use a couple of union to join
> >>>> then, but it takes toooo long.
>
> > Instead of UNION you could use OR in your WHERE clause.
>
> > Select * from sometable where name = 'JOHN'
> > union
> > Select * from sometable where salary > 500
> > union
> > Select * from sometable where state <> 'NY';
>
> > Select * from sometable where name = 'JOHN' OR salary > 500 OR state
> > <> 'NY';
>
> > If your queries take too long, try and index the tables. The index
> > fields must match your WHERE fields.
>
> Very inefficient compared with WITH.
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Ocultar texto de la cita -
>
> - Mostrar texto de la cita -

Thanks chris the problem is: the querys are morre restrictives to do
them in just 1 query ..

On Jul 5, 3:51 pm, valigula <valig...@gmail.com> wrote:
> On 5 jul, 19:50, DA Morgan <damor...@psoug.org> wrote:
>
>
>
>
>
> > Chris L. wrote:
> > >>> On Jul 5, 5:50 pm, valigula <valig...@gmail.com> wrote:
> > >>>> Hi All
> > >>>> Need top repeat a select 3 times but with differents where condition.
> > >>>> I tried to repeat the selects and after use a couple of union to join
> > >>>> then, but it takes toooo long.
>
> > > Instead of UNION you could use OR in your WHERE clause.
>
> > > Select * from sometable where name = 'JOHN'
> > > union
> > > Select * from sometable where salary > 500
> > > union
> > > Select * from sometable where state <> 'NY';
>
> > > Select * from sometable where name = 'JOHN' OR salary > 500 OR state
> > > <> 'NY';
>
> > > If your queries take too long, try and index the tables. The index
> > > fields must match your WHERE fields.
>
> > Very inefficient compared with WITH.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor...@x.washington.edu (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org-Ocultar texto de la cita -
>
> > - Mostrar texto de la cita -
>
> Thanks chris the problem is: the querys are morre restrictives to do
> them in just 1 query ..- Hide quoted text -
>
> - Show quoted text -

You said previously, you got it working using UNIONs, only it was "too
slow".

If you do something like this

select (something) from (somewhere) where (one condition)
union
select (something) from (somewhere) where (another condition)
union
select (something) from (somewhere) where (third condition);

Oracle will do (worst case) three full table scans (if there are no
relevant indexes).

Instead if you do

select (something) from (somewhere)
where (one condition) or (another condition) or (third condition);

Oracle will do one single full table scan (at least that's what my
explain_plan shows on Oracle 9.2.0.4.0 either with optimizer_mode=rule
or =choose)

If "somewhere" is more than one table, don't repeat the join
conditions on the WHERE clause.

I don't know how WITH would be more efficient in this case, I've tried
modifying the query to use WITH but got no better explain plan.

Greetings

Chris L. wrote:
> On Jul 5, 3:51 pm, valigula <valig...@gmail.com> wrote:
>> On 5 jul, 19:50, DA Morgan <damor...@psoug.org> wrote:
>>
>>
>>
>>
>>
>>> Chris L. wrote:
>>>>>> On Jul 5, 5:50 pm, valigula <valig...@gmail.com> wrote:
>>>>>>> Hi All
>>>>>>> Need top repeat a select 3 times but with differents where condition.
>>>>>>> I tried to repeat the selects and after use a couple of union to join
>>>>>>> then, but it takes toooo long.
>>>> Instead of UNION you could use OR in your WHERE clause.
>>>> Select * from sometable where name = 'JOHN'
>>>> union
>>>> Select * from sometable where salary > 500
>>>> union
>>>> Select * from sometable where state <> 'NY';
>>>> Select * from sometable where name = 'JOHN' OR salary > 500 OR state
>>>> <> 'NY';
>>>> If your queries take too long, try and index the tables. The index
>>>> fields must match your WHERE fields.
>>> Very inefficient compared with WITH.
>>> --
>>> Daniel A. Morgan
>>> University of Washington
>>> damor...@x.washington.edu (replace x with u to respond)
>>> Puget Sound Oracle Users Groupwww.psoug.org-Ocultar texto de la cita -
>>> - Mostrar texto de la cita -
>> Thanks chris the problem is: the querys are morre restrictives to do
>> them in just 1 query ..- Hide quoted text -
>>
>> - Show quoted text -
>
> You said previously, you got it working using UNIONs, only it was "too
> slow".
>
> If you do something like this
>
> select (something) from (somewhere) where (one condition)
> union
> select (something) from (somewhere) where (another condition)
> union
> select (something) from (somewhere) where (third condition);
>
> Oracle will do (worst case) three full table scans (if there are no
> relevant indexes).
>
> Instead if you do
>
> select (something) from (somewhere)
> where (one condition) or (another condition) or (third condition);
>
> Oracle will do one single full table scan (at least that's what my
> explain_plan shows on Oracle 9.2.0.4.0 either with optimizer_mode=rule
> or =choose)
>
> If "somewhere" is more than one table, don't repeat the join
> conditions on the WHERE clause.
>
> I don't know how WITH would be more efficient in this case, I've tried
> modifying the query to use WITH but got no better explain plan.
>
> Greetings

given that you didn't post your SQL there's not much anyone can say
other than 9.2.0.4? Applying a patch and getting closer to a supported
product might make a difference.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Jul 5, 8:15 pm, "Chris L." <diver...@uol.com.ar> wrote:
> On Jul 5, 3:51 pm, valigula <valig...@gmail.com> wrote:
>
>
>
> > On 5 jul, 19:50, DA Morgan <damor...@psoug.org> wrote:
>
> > > Chris L. wrote:
> > > >>> On Jul 5, 5:50 pm, valigula <valig...@gmail.com> wrote:
> > > >>>> Hi All
> > > >>>> Need top repeat a select 3 times but with differents where condition.
> > > >>>> I tried to repeat the selects and after use a couple of union to join
> > > >>>> then, but it takes toooo long.
>
> > > > Instead of UNION you could use OR in your WHERE clause.
>
> > > > Select * from sometable where name = 'JOHN'
> > > > union
> > > > Select * from sometable where salary > 500
> > > > union
> > > > Select * from sometable where state <> 'NY';
>
> > > > Select * from sometable where name = 'JOHN' OR salary > 500 OR state
> > > > <> 'NY';
>
> > > > If your queries take too long, try and index the tables. The index
> > > > fields must match your WHERE fields.
>
> > > Very inefficient compared with WITH.
> > > --
> > > Daniel A. Morgan
> > > University of Washington
> > > damor...@x.washington.edu (replace x with u to respond)
> > > Puget Sound Oracle Users Groupwww.psoug.org-Ocultartexto de la cita -
>
> > > - Mostrar texto de la cita -
>
> > Thanks chris the problem is: the querys are morre restrictives to do
> > them in just 1 query ..- Hide quoted text -
>
> > - Show quoted text -
>
> You said previously, you got it working using UNIONs, only it was "too
> slow".
>
> If you do something like this
>
> select (something) from (somewhere) where (one condition)
> union
> select (something) from (somewhere) where (another condition)
> union
> select (something) from (somewhere) where (third condition);
>
> Oracle will do (worst case) three full table scans (if there are no
> relevant indexes).
>
> Instead if you do
>
> select (something) from (somewhere)
> where (one condition) or (another condition) or (third condition);
>
> Oracle will do one single full table scan (at least that's what my
> explain_plan shows on Oracle 9.2.0.4.0 either with optimizer_mode=rule
> or =choose)
>
> If "somewhere" is more than one table, don't repeat the join
> conditions on the WHERE clause.
>
> I don't know how WITH would be more efficient in this case, I've tried
> modifying the query to use WITH but got no better explain plan.
>
> Greetings

Surely UNION changes the logic in the case where duplicates could be
returned, as well as adding a DISTINCT sort/hash operation that may be
unnecessary.

The idea of the WITH clause (subquery factoring) is for the case where
the "(somewhere)" in your query is something more complicated than a
single table, in which case you effectively declare it at the top and
reuse it multiple times.

HI all

Can any body help me with references where i could get Fake experience
in hyderabad or bangalore for ORACLE DBA . My friend is seriously
trying for it

you can mail me at : meetme_sharath@gmail.com

thank you