show innodb status
show InnoDB status often prints out 64-bit numbers in two pieces:
The high 32 bits and the low 32 bites. You can calculate the 64-bit number’s value by shifting the first number left 32 bits and adding it to the second one.
Examples:
LOG
—
Log sequence number 0 1188960
Log flushed up to 0 1188960
Last checkpoint at 0 1188960
In MySQL 5.1 and newer, you can select values directly from the INFORMATION_SCHEMA.GLOBAL_STATUS and INFORMATION_SCHEMA.SESSION_STATUS tables.
Counters are stored as unsigned integers. They use 4 bytes on 32-bit builds and 8 bytes on 64-bit builds. They wrap back to 0 after reaching their max values.
AES_ENCRYPT() and AES_ENCRYPT() – MySQL
SQL> SET @key = 'secret'
SQL> SET @encrypted = AES_ENCRYPT('my banking account password'), @key);
SQL> SELECT AES_DECRYPT(@encrypted, @key)
It doesn’t avoid the indexing issue; additionally, the data you’re trying to encrypt will still be in plain text in the SQL query, and it will be logged in the server’s log.
However, you can take steps further to help reduce the risk of other users seeing your secret data;
Store he encryption key in a user variable.
You can place the variable in a stored procedure and call the stored procedure to set its value, and then restrict access to the stored procedure and call the stored procedure to set its value, and then restrict access to the stored procedure.
WITH clause – Oracle
The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle optimizes the query by treating the query name as either an inline view or as a temporary table.
with q as (select 3.14 as one from dual)
select cos(one) from q
COS(ONE)
----------
-.99999873
innotop – MySQL
1. Download innotop from here
2. extract it to a local directory
3. Install (install Perl and the libdbd-mysql-perl package on Ubuntu if you have not done so).
perl Makefile.PL
You should get something like this:
Checking if your kit is complete…
Looks good
Writing Makefile for innotop
4. run innotop
#innotop -uroot -pRootPassword
Switch to a different mode:
B InnoDB Buffers I InnoDB I/O Info Q Query List
C Command Summary L Locks R InnoDB Row Ops
D InnoDB Deadlocks M Replication Status S Variables & Status
F InnoDB FK Err O Open Tables T InnoDB Txns
Actions:
a Toggle the innotop process k Kill a query's connection
c Choose visible columns n Switch to the next connection
d Change refresh interval p Pause innotop
e Explain a thread's query q Quit innotop
f Show a thread's full query r Reverse sort order
h Toggle the header on and off s Change the display's sort column
i Toggle idle processes x Kill a query
Other:
TAB Switch to the next server group / Quickly filter what you see
! Show license and warranty = Toggle aggregation
# Select/create server groups @ Select/create server connections
$ Edit configuration settings \ Clear quick-filters
MySQL UDF json
1. download the source code from here
2. extract it to a local directory (gunzip and tar -xvf)
3. sudo cp lib_mysqludf_json.so /usr/lib/mysql/plugin
4. mysql -uroot -p
5. mysql> source lib_mysqludf_json.sql
6. mysql>
Example: create an array from a table:
select json_array(
empno
, first_name
, last_name
) as emp
from employees;
MySQL option: i-am-a-dummy
–safe-updates, –i-am-a-dummy, -U
Permit only those UPDATE and DELETE statements that specify which rows to modify by using key values. If you have set this option in an option file, you can override it by using –safe-updates on the command line.
MyISAM table created in MySQL 5.0 with variable-length rows are configured by default to handle 256TB of data, using 6-byte pointers to the data records.
Earlier MySQL versions defaulted to 4-byte pointers, for up to 4GB of data.
.select (~0 >> 16)/1024/1024/1024/1024; +--------------------------------+ | (~0 >> 16)/1024/1024/1024/1024 | +--------------------------------+ | 255.9999999999990905 | +--------------------------------+ 1 row in set (0.00 sec) .select (~0 >> 32)/1024/1024/1024; +---------------------------+ | (~0 >> 32)/1024/1024/1024 | +---------------------------+ | 3.999999999069 | +---------------------------+ 1 row in set (0.00 sec)
Max value of Integers
SELECT ~0 as max_bigint_unsigned , ~0 >> 32 AS max_int_unsigned -- 8 bits * (8byte - 4 byte) = 32 bits , ~0 >> 40 AS max_mediumint_unsigned -- 8 bits * (8 bytes - 3 bytes) = 40 bits , ~0 >> 48 AS max_smallint_unsigned -- 8 bites * (8 bytes - 2 bytes) = 48 bits , ~0 >> 56 AS max_tinyint_unsigned -- 8 bites * ( 8 bytes - 1 byte) = 56 bits , ~0 >> 1 AS max_bigint_signed , ~0 >> 33 AS max_int_signed , ~0 >> 41 AS max_mediumint_signed , ~0 >> 49 AS max_smallint_signed , ~0 >> 57 AS max_tinyint_signed \G
MySQL replication filters
On the slave, the replicate_* options filter events as the salve SQL thread reads them from the relay log. You can choose to replicate or ignore one or more databases, or tables.
Database names in the filter are the filtered on the current default database.
mysql> USE tluo mysql> DELETE FROM tom.employees;
The *_do_db and *_ignore_db will filter the DELETE on the tluo, not on tom.
MySQL User Defined Functions
http://www.mysqludf.org/
Google mysql tools
http://code.google.com/p/google-mysql-tools/
http://code.google.com/p/mysql-master-master/
http://code.google.com/p/maatkit/
Generate globally unique IDs
1. Use auto_increment and auto_increment_offset
You can configure the servers to increment by two, set on server’s offset to onem and set the other’s to two. Now one server’s coluns will always contain even numbers, and the other’s will always contain oadd numbers.
2. Create a table in the glboal node
You can create a table with an AUTO_INCREMTN column in the global database node, and applications can use this to generate unique numbers.
3. Use memcached
There’s an incr() function in the memcached API that can increment a number atomically and return the result
Sharding function
Fixed sharding
Suppose you want 100 buckets,and you wnt to find out where to put user 123. If you’re using a modulus, the answer is easy: 123 modulus 100 is 23, so you should place the user into shard 11.
If, no the other hand, you’re using the CRC32() function for hashing, the answer is
SELECT CRC32(123) % 100; 22
The advantages of a fixed strategy are simplicity and low overhead. You can also hardcode it into the application.
Dynamic sharding
CREATE TABLE user_to_shard( user_id INT NOT NULL, shard_id INT NOT NULL, PRIMARY KEY(user_id)
define_editor – Oracle SQL*Plus
-- -- Copyright (c) 1988, 2005, Oracle. All Rights Reserved. -- -- NAME -- glogin.sql -- -- DESCRIPTION -- SQL*Plus global login "site profile" file -- -- Add any SQL*Plus commands here that are to be executed when a -- user starts SQL*Plus, or uses the SQL*Plus CONNECT command. -- -- USAGE -- This script is automatically run -- define_editor = 'vi'
I wrote this Ruby script so I can look up a word from command line.
#!/usr/bin/ruby
# tom.luo@gmail.com
# usage: save this script as z
# usage: chmod +x z
# usage: z word
# install json
# sudo gem install json
# $KCODE='utf'
require 'pp'
require 'net/http'
require 'uri'
require 'rubygems'
require 'json'
require 'cgi'
def translate( string, to='zh-CN', from='en' )
url = 'http://ajax.googleapis.com/ajax/services/language/translate'
params = {
:langpair => "#{from}|#{to}",
:q => string,
:v => 1.0
}
query = params.map{ |k,v| "#{k}=#{CGI.escape(v.to_s)}" }.join('&')
resp = Net::HTTP.get_response( URI.parse( "#{url}?#{query}" ) )
json = JSON.parse( resp.body )
if json['responseStatus'] == 200
json['responseData']['translatedText']
else
# return nil
raise StandardError, resp['responseDetails']
end
end
if __FILE__ == $0
string = ARGV[0] || 'oracle'
lang = ARGV[1] || 'zh-CN' # Chinese
puts translate( string, lang )
end
Using Oracle Repository Creation Utility (RCU) to create Metadata Repository
1. Download Repository Creation Utility from here.
2. Extract the package into the rcuHome directory.
cd /home/oracle/Downloads/oracle/rcuHome/rcu
3. ./rcu
I got this error when running rcu.
RCU-6107: DB Init Param Prerequisite failure for: processes
Current value is 200. It should be greater than or equal to 500.
Fix:
Connect as SYSDBA to database.
SQL>ALTER SYSTEM SET processes=500 SCOPE=SPFILE;
SQL>ALTER SYSTEM SET open_cursors=500 SCOPE=SPFILE;
# It complained about open_cursors after I increased processes to 500.
Restart database.
V$SGA and V$SGAINFO – Oracle
SQL> select * from v$sga; NAME VALUE -------------------- ---------- Fixed Size 2214936 Variable Size 381682664 Database Buffers 130023424 Redo Buffers 8015872 SQL> select * from v$sgainfo; NAME BYTES RES -------------------------------- ---------- --- Fixed SGA Size 2214936 No Redo Buffers 8015872 No Buffer Cache Size 130023424 Yes Shared Pool Size 239075328 Yes Large Pool Size 4194304 Yes Java Pool Size 4194304 Yes Streams Pool Size 0 Yes Shared IO Pool Size 0 Yes Granule Size 4194304 No Maximum SGA Size 521936896 No Startup overhead in Shared Pool 71303168 No NAME BYTES RES -------------------------------- ---------- --- Free SGA Memory Available 134217728 12 rows selected.
V$PROCESS – Oracle
The following three columns in the V$PROCESS view report the PGA memory allocated and used by an Oracle Database process:
PGA_USED_MEM
PGA_ALLOCATED_MEM
PGA_MAX_MEM
SQL> select pid,pname,pga_used_mem,pga_alloc_mem from v$process order by pga_alloc_mem
SQL> /
PID PNAME PGA_USED_MEM PGA_ALLOC_MEM
———- —– ———— ————-
1 0 0
18 S000 409220 647312
4 GEN0 697048 778384
5 DIAG 689872 778384
16 MMNL 707072 778384
7 PSP0 693024 778384
22 QMNC 694176 778384
9 MMAN 693024 778384
20 SMCO 689784 778384
3 VKTM 689784 778384
2 PMON 694480 778384
PID PNAME PGA_USED_MEM PGA_ALLOC_MEM
———- —– ———— ————-
28 VKRM 689832 778384
27 J001 689880 843920
26 Q000 806640 909456
21 W000 809920 909456
6 DBRM 768928 974992
12 CKPT 763840 1054272
17 D000 1076380 1368208
8 DIA0 1167192 1416688
14 RECO 768672 1433744
25 J000 693816 1892496
23 1146488 1900864
PID PNAME PGA_USED_MEM PGA_ALLOC_MEM
———- —– ———— ————-
15 MMON 1936904 4308640
24 Q001 4043352 5414384
19 CJQ0 1183344 5964096
13 SMON 1107328 6086800
10 DBW0 6648896 6897440
11 LGWR 11321600 12033520
28 rows selected.
SQL>
The Fibonacci numbers occur as the ratio of successive convergents of the continued fraction for , and the matrix formed from successive convergents of any continued fraction has a determinant of +1 or −1.
The matrix representation gives the following closed expression for the Fibonacci numbers:
Install the numpy module first.
sudo apt-get install python-numpy
>a
matrix([[1, 1],
[1, 0]])
>c = a*a
>c
matrix([[2, 1],
[1, 1]])
>c = a*a*a
>c
matrix([[3, 2],
[2, 1]])
>c = a*a*a*a
>d = a** 4
>c
matrix([[5, 3],
[3, 2]])
[oracle@oel64 ~]$ sqlplus /
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 15 15:38:26 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
show parameters target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 500M memory_target big integer 500M parallel_servers_target integer 8 pga_aggregate_target big integer 0 sga_target big integer 0
select * from v$memory_target_advice;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
375 .75 950 1.0273 0
500 1 925 1 0
625 1.25 925 1 0
750 1.5 925 1 0
875 1.75 925 .9999 0
1000 2 925 .9999 0
6 rows selected.
The SHARED_SERVERS initialization parameter specifies the MINIMUM number of shared servers that you want created when the instance is started.
In typical systems, the number of shared servers stabilizes at a ratio of ONE shared server for every TEN connections.
The PMON (process monitor) background process cannot terminate shared servers below the value specified by SHARED_SERVERS.
Assume a database is being used by a call center staffed by 500 agents during the day. On average, each agent spends 90% of the time talking to customers and only 10% of the time querying and modifying records. To keep the shared servers from being terminated as agents talk to customers and then spawned again as agents access the database, a DBA specifies that the optimal number of shared servers is 50
Suppose we have an application in which the memory required for each session is 400 KB and the memory required for each server process is 4 MB. The pool size is 100 and the number of shared servers used is 100.
If there are 4000 client connections, the memory used by each configuration is as follows:
Dedicated Server
Memory used = 4000 client connections * (400 KB + 4 MB) = 17.56 GB
Shared Server
Memory used = 4000 * 400 KB + 100 shared servers * 4 MB = 1.96 GB
Database Resident Connection Pooling
Memory used = 100 pool size * 400 KB + 100 shared server * 4 MB + 4000 * 35KB= 575 MB
Build Oracle Data Dictionary
Run Scripts to Build Data Dictionary Views
SQL>@?/rdbms/admin/catalog.sql SQL>@?/rdbms/admin/catproc.sql SQL>@?/sqlplus/admin/pupbld.sq
The at-sign (@) is shorthand for the command that runs a SQL*Plus script. The question mark (?) is a SQL*Plus variable indicating the Oracle home directory.
Create database
Example 1.
CREATE DATABASE tomluo
USER SYS IDENTIFIED BY sys_password
USER SYSTEM IDENTIFIED BY system_password
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/tomluo/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/tomluo/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/tomluo/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/app/oracle/oradata/tomluo/system01.dbf' SIZE 400M REUSE
SYSAUX DATAFILE '/u01/app/oracle/oradata/tomluo/sysaux01.dbf' SIZE 400M REUSE
DEFAULT TABLESPACE users
DATAFILE '/u01/app/oracle/oradata/tomluo/users01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/tomluo/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/app/oracle/oradata/tomluo/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
The WE8ISO8859P1 character set is used to store data in this database.
The AL16UTF16 character set is specified as the NATIONAL CHARACTER SET, used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2
Example 2. Create a database with Oracle Managed Files
Set the initialization parameter DB_CREATE_FILE_DEST.
CREATE DATABASE tomluo USER SYS IDENTIFIED BY sys_password USER SYSTEM IDENTIFIED BY system_password EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE temp UNDO TABLESPACE undotbs1 DEFAULT TABLESPACE users;
Set up OS Authentication – Oracle
Suppose your Linux/Unix username is ‘oracle’
When you try to connect as an OS authenticated user. We expect it to fail.
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
sqlplus /
ERROR:
ORA-01017: invalid username/password; logon denied
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameters os_authent_prefix
NAME TYPE VALUE
———————————— ———– ——————————
os_authent_prefix string ops$
As you can see, the default value of os_authent_prefix is ‘ops$’.
Now we know the OS authentication prefix, we can create a database user to allow OS authentication.
SQLPLUS SYS AS SYSDBA
SQL>CREATE USER ops$oracle IDENTIFIED EXTERNALLY;
SQL> GRANT CONNECT TO ops$oracle
– if you want to grant dba to the ‘oracle’ OS user.
SQL> GRANT DBA TO ops$oracle
You need to set SQLNET.AUTHENTICATION_SERVICES = (NTS) on the ugly Windows in %ORACLE_HOME%\network\admin\sqlnet.ora”
Now you can connect to the database using OS authentication.
SQLPLUS / SYSDBA
The maximum number of characters for ORACLE_SID is 12
The maximum number of characters for the database name is 8.
t is common practice to set the SID to be equal to the database name.
Error: The total number of locks exceeds the lock table size
Cause: ‘innodb_buffer_pool_size” is not set in my.cnf so the default value (8M) is used.
Solution:
sudo vi /etc/my.cnf
increase innodb_buffer_pool_size
if cell_types[col] == xlrd.XL_CELL_DATE:
cell_val = datetime.datetime(*xlrd.xldate_as_tuple(sh.cell_value(row,col), book.datemode))
else:
cell_val = str(sh.cell_value(row,col)).encode('utf8')
Delete blank line – vim
:g/^$/d
phoneNumberPattern = re.compile(r'''
# don't match beginning of string, number can start anywhere
(\d{3}) # area code is 3 digits (e.g. '800')
\D* # optional separator is any number of non-digits
(\d{3}) # trunk is 3 digits (e.g. '298')
\D* # optional separator
(\d{4}) # rest of number is 4 digits (e.g. '6622')
\D* # optional separator
(\d*) # extension is optional and can be any number of digits
$ # end of string
''', re.VERBOSE)
phonePattern.search('work 1-(800) 298.6622 #1234').groups()
('800', '298', '6622', '1234')
# Tom Luo
# oul.mot@gmail.com
# 2010-08-03
1. To show all existing groups
cat /etc/group
2. Create the oinstall group if it doesn’t exist
groupadd oinstall
3. Create the dba group if it doesn’t exist
groupadd dba
4. Create the oracle user if it doesn’t exist
useradd -u 1000 -g oinstall -G dba -d /home/oracle/ -r oracle
Modify the groups if you already created the oracle user
usermod -g oinstal -G dba oracle
5. Set the password for the oracle user
passwd oracle
Repeat step 1 through 5 on each node in your cluster
6. Verify the attributes of the oracle user
id oracle
7. Configure the RSA and DSA keys
Log back as oracle on rac1
8. create the .ssh directory if it doesn’t exist
ls -a $HOME
mkdir ~/.ssh
chmod 700 ~/.ssh
9. Create the RSA-type public and private keys.
ssh-keygen -t rsa
accept the default location /home/oracle/.ssh/id_rsa.pub
and /home/oracle/.ssh/id_rsa
Enter a pass phrase when prompted
10. Create the DSA type public and private keys on rac1 and rac2
ssh-keygen -t dsa
11. Adding the keys to the Authorized key file
cd ~/.ssh
cat id_rsa.pub >> authorized_keys
cat id_dsa.pub >> authorized_keys
12. Copy the authorized_keys file from rac1 to rac2
scp authorized)keys rac2:/home/oracle/.ssh/
13. Using SSH, log in to rac2
ssh rac2
cd .ssh
cat id_rsa.pub >> authorized_keys
cat id_dsa.pub >> authorized_keys
Note: id_rsa.pub and id_dsa are created in step 1 through step 5
14. Copy the updated authorized_keys file back to rac1
scp authorized_keys rac1:/home/oracle/.ssh/
At this point, if you use SSH to log in to or run a command on another node, you are prompted for the pass phrase that you specified when you create the RSA and DSA keys.
15. On the system where you want to run Oracle Universal Installer, log in as oracle
16. Start the SSH agent and load the SSH keys into memory
ssh-agent $SHELL
ssh-add
17. Complete the SSH configuration by using the ssh command to retrieve the date on rac1 and rac2 in the cluster
$ssh rac1 date
$ssh rac2 date
The first time you run, you will be prompted to accept the RSA key fingerprint, enter yes to continue
You should NOT be prompted for a password or passphrase when you connect to rac1 to rac2.
User equivalency must meet the following requirements:
1. A given user has the same user name, UID, and password
2. A given user belongs to the same groups with the same Group ID
Make sure you get the same results on rac1 and rac2
$id oracle
uid=1000(oracle) gid=1002(oinstall) groups=1002(oinstall),1003(dba)
PLS_INTEGER – Oracle
PLS_INTEGER Datatype
You use the PLS_INTEGER datatype to store signed integers. Its magnitude range is -2147483648 to 2147483647, represented in 32 bits. PLS_INTEGER values require less storage than NUMBER values and NUMBER subtypes. Also, PLS_INTEGER operations use hardware arithmetic, so they are faster than NUMBER operations, which use library arithmetic. For efficiency, use PLS_INTEGER for all calculations that fall within its magnitude range. For calculations outside the range of PLS_INTEGER, you can use the INTEGER datatype.
SQL> select power(2,31) from dual; POWER(2,31) ----------- 2147483648
maxFactor – Python
#!/usr/bin/python
"""
This function returns the largest factor of a number
"""
__author__ = "oul.mot@gmail.com"
__version__ = "$Revision 0.1 $"
__date__ ="$Date: 2010/08/01 19:11PM CST$"
__license__ = "Python"
def maxFactor(n):
"return the largest factor of n"
count = n / 2
while count > 1:
if n % count == 0:
return count
if __name__ == "__main__":
import sys
if len(sys.argv) == 2:
from timeit import Timer
statement = "maxFactor(int(sys.argv[1]))"
t = Timer(statement, "from __main__ import maxFactor")
print maxFactor(int(sys.argv[1])), min(t.repeat())
else:
print "Usage: factor n"
Dictionary keys -Python
All immutable data types are hashable, so they can be used as dictionay keys.
Note that the integer 1 and the float 1.0 hash to the same value i.e. they areidentical as keys.
>d={}
>d[1]='a'
>d
{1: 'a'}
>d[1.0]='b'
>d
{1: 'b'}
>
if-elif-else – Python and Oracle
Python
if expression1:
if_suite
elif expression2:
elif_suite
else:
else_suite
‘elif’[::-1] = ‘file’
# reverse(‘elif’) = ‘file’
Oracle
IF expression1 THEN
if_suite
ELSIF expression2 THEN
elsif_suite
ELSE
else_suite
END IF;
Number format element – Oracle
‘RN’ returns a value as Roman numerals in uppercase
SQL> SELECT TO_CHAR(2010,’RN’) FROM DUAL;
TO_CHAR(2010,’R
—————
MMX
# Implementation in Python
import re
#Define exceptions
class RomanError(Exception): pass
class OutOfRangeError(RomanError): pass
class NotIntegerError(RomanError): pass
class InvalidRomanNumeralError(RomanError): pass
#Define digit mapping
romanNumeralMap = (('M', 1000),
('CM', 900),
('D', 500),
('CD', 400),
('C', 100),
('XC', 90),
('L', 50),
('XL', 40),
('X', 10),
('IX', 9),
('V', 5),
('IV', 4),
('I', 1))
def toRoman(n):
"""convert integer to Roman numeral"""
if not (0 < n < 5000):
raise OutOfRangeError, "number out of range (must be 1..4999)"
if int(n) <> n:
raise NotIntegerError, "non-integers can not be converted"
result = ""
for numeral, integer in romanNumeralMap:
while n >= integer:
result += numeral
n -= integer
return result
PREDICTION function – Oracle
This example returns the average age of customers who are likely to use an affinity card.
SELECT gender, COUNT(*) AS cnt, ROUND(AVG(age)) AS avg_age
FROM mining_data
WHERE PREDICTION(DT_SH_Clas_sample COST MODEL
USING marital_status, education, household_size) = 1
# The PREDICTION function takes into account only the marital_status, education, and household_size predictors.
GROUP BY gender
ORDER BY gender;
C CNT AVG_AGE
- ———- ———-
F 170 38
M 685 42
Analytics Functions – Oracle
RATIO_TO_REPORT is an analytic function. It computes the ratio of a value to the sum of a set of values. If expr evaluates to null, then the ratio-to-report value also evaluates to null.
SELECT ename, sal, ratio_to_report(sal) over (partition by deptno)
FROM emp
It returns the employee name, salary, and the ration of the salary to the sum salary of all employees partitioned by department no.
WIDTH_BUCKET lets you construct equiwidth histograms, in which the histogram range is divided into intervals that have identical size
SELECT customer_id, cust_last_name, credit_limit,
WIDTH_BUCKET(credit_limit, 100, 5000, 10) “Credit Group”
FROM customers WHERE nls_territory = ‘Canada’
ORDER BY “Credit Group”;
sudo vi /etc/NetworkManager/nm-system-settings.conf
and set managed=true
Soundex – Python
Soundex algorithm:
1. Keep the first letter.
2. Convert the remaining letters to digits, according to mapping below:
abcdefghijklmnopqrstuvwxyz => 91239129922455912623919292
The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:
[From Oracle 10g documentation]
Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
Assign numbers to the remaining letters (after the first) as follows:
b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6
If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.
Return the first four bytes padded with 0.
3. Remove consecutive duplicates.
4. Remove all 9s.
5. If the result is shorter than four characters (the first letter plus three digits), pad the result with trailing zeros.
6. if the result is longer than four characters, discard everything after the fourth character.
import string
charToSoundex = string.maketrans(string.letters, "91239129922455912623919292" * 2)
def soundex(source):
if not isalpha(source):
return "0000"
s = source[0].upper() + source[1:].translate(charToSoundex)
s2 = s[0]
for d in s[1:]:
if s2[−1] != d:
s2 += d
s3 = re.sub('9', '', s2)
while len(s3) < 4:
s3 += "0"
return s3[:4]
Hierarchical Query – Oracle
Oracle’s START WITH and CONNECT BY clauses in the SELECT statement traverse a hierarchy.
Without this feature, n number of self-joins (n = max_levels) would be required.
ORDER SIBLINGS BY ename will preserve the hierarchy and also sort the ename within each level.
Prior to Oracle 10g, a circular loop in the hierarchy would return an error, ” ORA-01436: CONNECT BY loop in user data”.
In Oracle 10g, It can be avoided by the NOCYCLE
connect by nocycle prior empno = mgr
Syntax
Oracle 9i
Oracle 10g
Oracle 11g
Data-centric Programming
Busywork code is not important. Data is important. And data is not difficult. It’s only data. If you have too much, filter it. If it’s not what you want, map it. Focus on the data; leave the busywork behind
– Dive into Python
Here are some of my examples
def odd(i):
return i % 2
a = range(10)
b = filter(odd, a)
a
[0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
b
[1, 3, 5, 7, 9]
x=[1,-2,3,4,-34]
y = filter(lambda z: z > 0, x)
y
[1,3,4]
# list comprehension
d = [i*2 for i in a]
d
[0, 2, 4, 6, 8, 10, 12, 14, 16, 18]
map(len,["Oracle","MySQL"])
[6,5]
You could do the same thing with a for loop. Functions like filter are much more expressive. Not only is it easier to write, it’s easier to read, too. Reading the for loop is like walking in woods, you see all the details, but you won’t see the bigger picture until you are out of the woods.
c = []
for n in a:
if odd(n):
c.append(n)
c
[1, 3, 5, 7, 9]
Copying a block of text – vi
1. Go to the the top line to be copied
2. ma: Mark this line as ‘a’
3. Go to the bottom line to be copied
4. y’a: yank the text from the current cursor location to the mark “a”
5. p: paste the text
Visual mode
1. Go to the the top line to be copied
2. v: start visual mode
3. Go to the bottom line to be copied
4. y: yank the highlighted text
5. p: paste the text
The Zen of Python
Online methods doc – Python
cat methods_doc.py
author: Mark Pilgrim
def info(object, spacing=10, collapse=1): """Print methods and doc strings. Takes module, class, list, dictionary, or string.""" methodList = [e for e in dir(object) if callable(getattr(object, e))] processFunc = collapse and (lambda s: " ".join(s.split())) or (lambda s: s) print "\n".join(["%s %s" % (method.ljust(spacing), processFunc(str(getattr(object, method).__doc__))) for method in methodList])
from methods_doc import info
info([])
info({})
info(1)
Unicode in Python
nihao=U'''\u3053\u3093\u306B\u3061\u306F .\u4F60\u597D''' print nihao # こんにちは # 你好
https://help.ubuntu.com/community/AppleKeyboard
If you make the changes in /etc/rc.local, make sure the file is executable.
chmod +x rc.local