(INNER table predicate in where-clause vs ON-clause is well covered online and also in my own http://bigblog.tanbin.com/2011/05/whats-different-between-these-2-sqls.html)
Note intermediate table is unaffected by where-clause. Query processor always uses on-clause exclusively to build an intermediate joined table before applying where-clause. This is the logical view we can safely assume. Physically, Query processor could optimize away the where/on distinction, but output is always consistent with the logical view.
Q1: can we _always_ assume that LL-outerjoin-RR intermediate table _always_ includes all LL rows if no outer table predicate in ON-clause?
A: yes with the big “if”
%%A: If you need to filter on outer table, do it in where-clause — better understood. Avoid ON-clause.
Q2: Boldly dropping the big “if”, can we _always_ assume that LL-outerjoin-RR intermediate table _always_ includes all LL rows _regardless_ of outer table predicate in ON-clause?
%%A: probably yes.
If you really really want outer table predicate in ON-clause, I assume you have no good reason and just feels adventurous.
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32300.1502/html/sqlug/sqlug169.htm shows that even with outer table predicate in ON-clause, we still get “… all LL rows”
select title, titles.title_id, price, au_id
from titles left join titleauthor
on titles.title_id = titleauthor.title_id
and titles.price > $20.00
title title_id price au_id
-------------------- --------- ------ ---------------
The Busy Executive’s BU1032 19.99 NULL
Cooking with Compute BU1111 11.95 NULL
You Can Combat Compu BU2075 2.99 NULL
Straight Talk About BU7832 19.99 NULL
Silicon Valley Gastro MC2222 19.99 NULL
The Gourmet Microwave MC3021 2.99 NULL
The Psychology of Com MC3026 NULL NULL
But Is It User Friend PC1035 22.95 238-95-7766
“The rand function uses the output of a 32-bit pseudo-random integer generator. The integer is divided by the maximum 32-bit integer to give a double value between 0.0 and 1.0”
There’s a non-zero chance of getting the max integer, which gives 1 when divided by max. Same probability for 0/max which gives 0.
convert (int, rand()*3) can return 0, 1, 2 and 3, with a non-zero probability for 3 and equal distribution among the 0, 1 and 2.The non-zero chance is something like 2–32 or 2**(-32) in python syntax.
In one of my sybase servers with large database load, we managed to mark one of 16 cpu cores to a specific stored proc, so no other process could use that core.
We also managed to dedicate a processor to a specific JDBC connection.
This let us ensure a high priority task gets enough CPU resource allocation.
What’s the name of the feature?
Truth – in my Sybase 12.5.4 server, it is safe to pass @misspeltParam=12345 into any proc. This param can be first, last (or anywhere) param in the SQL.
Not sure if it is due to some server config.
— based on P22 [[ new features guide to ASE 15 ]]
The holy grail — let users determine the objects, schedules and datachange thresholds to automate the stat-update process.
 “update only when required”
– datachange is a function used in “select datachange(….”
– datachange function returns a percentage — how many percent of data changed, due to CUD.
Many people feel Sybase is unsuitable for large tables. How about a 25GB table?
I worked with a few post trading systems (commissions, trade-level revenues, settlement…), where each table’s data occupy 10 – 25GB, with another 10 – 25GB for all indices of that table. Each row is typically 1 – 2KB, typically within a Sybase data page, so such a table typically hold 10+ million rows.
My Sybase trainer said Sybase is faster than oracle/db2 for small tables below 100mil rows.
My database colleagues also feel Sybase is fast with 10-25GB/table.
Tip: multi-line string literal is supported
Tip: exec (@full_select) — parentheses needed
Tip: to embed parameters in the query —
@full_select + ” and ac_account_number = ‘” + @acct + “‘”