outer table predicate in ON-clause #my take

(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
Advertisements

sybase rand() could return 0 and 1

“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 232 or 2**(-32) in python syntax.

sybase 15 dedicated cpu

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?

datachange && auto update-stat in sybase 15

— based on P22 [[ new features guide to ASE 15 ]]

The holy grail — let users determine the objects, schedules and datachange thresholds[1] to automate the stat-update process.

[1] “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.

25G/table – sybase comfort zone

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.

q(OUTPUT) param in sybasae

Sound byte — You write “OUTPUT” once each in the service proc AND the client proc.

[ Service proc ] CREATE procedure serviceProc (@p1,@p2,… @lastParam int OUTPUT) /* usually last param */
[ Service proc ] /* in the body */ select @lastParam = 123

1) [ client proc ] exec @ret = serviceProc ‘valueFor_p1’,’valueFor_p2’…. @someVarDeclaredLocally OUTPUT
2) [ client proc ] exec @ret = serviceProc @p1=..,@p2=…. @lastParam = @someVarDeclaredLocally OUTPUT

Note the strange syntax in @lastParam = @someVarDeclaredLocally — assigning left to right!

Note @someVarDeclaredLocally must declared locally. @lastParam must NOT since it’s not a variable at all. It’s a TAG.

In both 1) and 2), you don’t provide a value for @lastParam (like you do @p1), but you specify a L-value variable to RECEIVE the output

how to get java to capture printing from sybase stored proc

In my experience on Wall St, Sybase store proc can get very complex. A basic technique is the lowly “print”. It beats “select” because under error condition all selects into a log table are rolled back.

Sometimes Sybase print output doesn’t get returned to java. For jdbc I had a simple reusable method to while-loop through a series of warnings. Here’s my technique for spring jdbcTempalte. Note the documented “logging all warnings” may not work. If you don’t override handleWarnings() like I did, then all warnings become exceptions so super.query() return value is lost — real show stopper.

public class GenericProcedureCaller extends JdbcTemplate {
@Override
protected void handleWarnings(Statement stmt) {
try {
super.handleWarnings(stmt);
} catch (SQLWarningException e) {
log.info("\t\t v v v output from database server v v v v ");
SQLWarning warn = e.SQLWarning();
while (warn != null) {
log.info(warn);
warn = warn.getNextWarning();
}
log.info("\t\t ^ ^ ^ output from database server ^ ^ ^ ^ ");
} catch (SQLException e) {
e.printStackTrace();
}
}
@Override
public List query(String sql, RowMapper rowMapper) throws DataAccessException {
boolean oldSetting = isIgnoreWarnings();
// setting to false to capture "prints" from the proc, but there's side effect.
setIgnoreWarnings(false);
try {
return super.query(sql, rowMapper);
} finally {
setIgnoreWarnings(oldSetting);
}
}