databases

- READ UNCOMMITTED
- READ COMMITTED (protecting against dirty reads)
- REPEATABLE READ (protecting against dirty and non-repeatable reads)
- SERIALIZABLE (protecting against dirty, non-repeatable reads and phantom reads)

https://www.youtube.com/watch?v=zz-Xbqp0g0A database isolation level

https://en.wikipedia.org/wiki/Isolation_(database_systems)

Foundations of Databases by Serge Abiteboul, Richard Hull, Victor Vianu

http://queue.acm.org/detail.cfm?id=2696453

http://www.e-booksdirectory.com/details.php?ebook=7942

http://blog.confluent.io/2015/03/04/turning-the-database-inside-out-with-apache-samza/

https://news.ycombinator.com/item?id=9145197

http://habrahabr.ru/company/yandex/blog/273305/

InfuxDB Go

https://blog.selectel.ru/time-series-metriki-i-statistika-vvedenie-v-influxdb/

https://influxdb.com/blog/2015/10/07/the_new_influxdb_storage_engine_a_time_structured_merge_tree.html

http://www.snowflake.net/

http://www.databasefriends.co/

Move code to database stored procedures

https://news.ycombinator.com/item?id=9481211

https://sivers.org/pg

OrientDB

http://dataandco.expertly.io/post/113253941965/a-new-adventure-leaving-mongodb-and-joining

Redis Memcache

http://stackoverflow.com/questions/2873249/is-memcached-a-dinosaur-in-comparison-to-redis/8494397#8494397

http://stackoverflow.com/questions/23601622/if-redis-is-already-a-part-of-the-stack-why-is-memcached-still-used-alongside-r

http://key-value-stories.blogspot.com/2015/02/memcached-internals-design.html and Redis

https://news.ycombinator.com/item?id=9052318 PipelineDB

http://www.slideshare.net/tsarevoleg/ss-40969331

http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back

http://blog.acolyer.org/2015/01/20/architecture-of-a-database-system/

http://www.infoq.com/presentations/acid-stream-processing

http://fnordmetric.io/chartsql/

http://blog.modeanalytics.com/open-sourcing-our-analysis/

http://patshaughnessy.net/2014/10/13/following-a-select-statement-through-postgres-internals

http://pipesql.com/

https://crate.io/

http://prestodb.io/

https://github.com/rxin/db-readings

https://infosys.uni-saarland.de/datenbankenlernen/

http://www.essentialsql.com/what-is-a-database-index/

http://db.cs.berkeley.edu/cs286/papers/anatomy-redbook2005.pdf

median in SQL

http://datamonkey.pro/blog/median_in_sql/

Implementation of database systems

http://www.cs286.net/home/reading-list

http://www.hakkalabs.co/articles/thumbtack-nosql-database-comparison-by-ben-engber

http://labs.codernity.com/codernitydb/ pure python noSQL database

http://chango.github.io/hustle/

http://symas.com/mdb/

http://habrahabr.ru/post/214647/ NoSQL

http://www.infoq.com/news/2014/06/facebook-apollo FaceBook Appolo

https://news.ycombinator.com/item?id=7175830

https://periscope.io/blog/use-subqueries-to-count-distinct-50x-faster.html

http://0xdata.com/ prediction engine for big data science mountain view

http://nosql.mypopescu.com/

http://db-engines.com

http://www.datanami.com/

http://www.dbms2.com/

http://tm.durusau.net/

https://www.facebook.com/notes/mohan-c-mohan/c-mohans-tutorial-an-in-depth-look-at-modern-database-systems/10151905506077295

http://vladmihalcea.com/2014/04/17/the-anatomy-of-connection-pooling/

http://habrahabr.ru/post/208400/ MVCC

http://www.enterprisedb.com/postgres-plus-edb-blog/amit-kapila/well-known-databases-use-different-approaches-mvcc

https://periscope.io/blog/count-distinct-in-mysql-postgres-sql-server-and-oracle.html

http://leopard.in.ua/2013/11/08/nosql-world/

http://www.slideshare.net/alexandre_morgaut/nosql-and-javascript-a-love-story-16052552

https://news.ycombinator.com/item?id=6466222 Benchmark: Redshift, Hive, Shark, Impala

Conferences

http://www.xldb.org/

https://conf-slac.stanford.edu/xldb-2013/conference-program XLDB

http://strataconf.com/stratany2013/public/schedule/proceedings

http://blog.aggregateknowledge.com/ak-data-science-summit-june-20-2013/

http://geteventstore.com/

Anja Bog, "Benchmarking Transaction and Analytical Processing Systems

https://epic.hpi.uni-potsdam.de/Home/CBTR (Java Source Code)

DREMEL

http://habrahabr.ru/post/207234/

http://www.computerra.ru/85688/google-dremel-giperdvigatel-analiticheskoy-obrabotki-dannyih/

PRESTO

http://prestodb.io/

https://news.ycombinator.com/item?id=6684318

REDSHIFT ACTION PARACCEL

http://www.linkedin.com/groups/Redshift-Professionals-4884099

http://jobview.monster.com/amazon-redshift-aws-software-development-engineer-job-palo-alto-ca-us-127040200.aspx

http://www.packtpub.com/getting-started-with-amazon-redshift/book

http://dailytechnology.net/2013/08/03/redshift-what-you-need-to-know/

http://blog.aggregateknowledge.com/2013/05/16/aws-redshift-how-amazon-changed-the-game/

http://stackoverflow.com/questions/tagged/amazon-redshift

http://docs.aws.amazon.com/redshift/latest/dg/doc-history.html

http://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html

RaptorDB

http://www.codeproject.com/Articles/316816/RaptorDB-The-Key-Value-Store-V2

http://www.reddit.com/r/programming/comments/1t6266/an_alternative_to_btrees/

http://www.vldb.org/

http://www.linkedin.com/in/marcinzukowski

http://sandeeptata.blogspot.com/

http://dba.stackexchange.com/

http://incubator.apache.org/drill/ Apache Drill

http://metamarkets.com/blog/

SHARDING

https://habrahabr.ru/company/oleg-bunin/blog/313366/

http://www.codefutures.com/database-sharding/

Bitmap Indexes in Databases

http://kellabyte.com/2013/03/05/using-bitmap-indexes-in-databases/

http://lemire.me/fr/documents/thesis/EdThesis.pdf

https://code.google.com/p/lemurbitmapindex/

http://siganakis.com/using-bitmap-indexes-in-query-processing

http://bigfastdata.blogspot.com/2013/06/life-beyond-column-stores-exploiting.html

http://blog.aggregateknowledge.com/ak-data-science-summit-june-20-2013/

http://www.vldb.org/pvldb/vol6/p1190-viglas.pdf Just-In-Time SQL compilation

Hekaton: SQL Server’s Memory-Optimized OLTP Engine

http://research.microsoft.com/pubs/193594/Hekaton%20-%20Sigmod2013%20final.pdf

Buffer Management:

http://msdn.microsoft.com/en-us/library/aa337525%28v=sql.105%29.aspx

Graph Procesing

http://db.disi.unitn.eu/pages/VLDBProgram/pdf/IMDM/paper1.pdf

Simple and safe SQL queries with C++ templates

http://www.cs.technion.ac.il/~lkeren/scp.pdf

http://sigops.org/sosp/sosp13/program.html many pdf articles

http://www.ibm.com/developerworks/library/bd-sqltohadoop1/index.html HADOOP integration

http://www.dbspecialists.com/files/presentations/semijoins.html

NUMA-AWARE hash join

http://db.disi.unitn.eu/pages/VLDBProgram/pdf/IMDM/paper4.pdf

Pipelined query evaluation

http://www.cs.bu.edu/faculty/gkollios/db12/

http://web.cs.wpi.edu/~cs542/f09/

http://www.slideshare.net/GraySystemsLab/pass-summit-2010-keynote-david-dewitt

CPU cache

http://blog.kejser.org/2012/06/14/the-effect-of-cpu-caches-and-memory-access-patterns/

http://sqlblog.com/blogs/joe_chang/archive/2011/02/07/simd-extensions-for-the-database-storage-engine.aspx

http://www.mikeash.com/pyblog/friday-qa-2012-12-28-what-happens-when-you-load-a-byte-of-memory.html

http://lwn.net/Articles/250967/

the approximate different levels of latency for accessing the different levels of cache within the Intel i7 Xeon range of processors

Column Databases

http://nms.csail.mit.edu/~stavros/pubs/tutorial2009-column_stores.pdf

http://habrahabr.ru/company/oracle/blog/257745/

http://www.exasol.com/en/

https://news.ycombinator.com/item?id=9250102

column-oriented optimizations

compression,

late materialization

block iteration

invisible join

1) each predicate is performed on the

relevant dimension table to retrieve the list of table keys

, each hash table with the relevant keys is

used to retrieve the positions of the records in the fact table

satisfying the corresponding predicate. As it can be seen in

Figure 3, hash table are probed with the foreign key-columns

of the fact table and it returns the positions representing the

fields satisfying the predicate. Then, it returns the intersection

of our tables using bitwise and.

final phase uses the final position table for each column

of the fact tables which has a foreign key reference to the

dimension tables, to get answer to the query. As can be seen in

Figure 4, each foreign key value from fact tables are extracted

using our position table values and then information is

extracted from the needed dimension tables using these

positions.

Summary

As a result, it can be said that since the number of positions

in the position table is dependent on the selectivity of the

whole query (not just a part of it), the necessary number of

extraction of values is minimized and this improves the

performance.

http://airccj.org/CSCP/vol2/csit2343.pdf in PostgeSQL

http://www.coep.org.in/page_assets/341/121022008.pdf

Read-Optimized Databases, In Depth

http://pages.cs.wisc.edu/~ahollowa/paper377.pdf

http://www.ijarcsse.com/docs/papers/April2012/Volume_2_issue_4/V2I40079.pdf

http://homepages.cwi.nl/~boncz/msc/2010-FabianNagel.pdf

http://hal.archives-ouvertes.fr/docs/00/75/21/50/PDF/202.pdf

http://www.scribd.com/doc/92371275/Column-Oriented-DB-Systems

http://siganakis.com/using-bitmap-indexes-in-query-processing

http://blog.kejser.org/2012/07/04/how-do-column-stores-work/

https://www.cs.helsinki.fi/webfm_send/1006/Sezin_final.pdf

http://paperhub.s3.amazonaws.com/14d147739ca381a610b8eea771ab0c84.pdf

http://www.edbt.org/Proceedings/2011-Uppsala/papers/edbt/a11-liu.pdf

http://arxiv.org/pdf/0909.1346.pdf

http://arxiv.org/abs/0901.3751

http://www.public.asu.edu/~ychen127/pvldb10_cods.pdf

http://www.cc.gatech.edu/~lingliu/courses/cs4440/notes/17.BigTableColumnDB.pdf

Internals Open Source

http://nms.csail.mit.edu/~stavros/pubs/tutorial2009-column_stores.pdf

http://www.postgresql.org/docs/8.0/static/internals.html PostgreSQL

http://www.postgresql.org/docs/devel/static/internals.html

http://www.cse.iitb.ac.in/dbms/Data/Courses/CS631/PostgreSQL-Resources/pg_internalpics.pdf

http://madlib.net/

http://hypertable.org/

http://crd-legacy.lbl.gov/~kewu/ps/PUB-3192.html

http://crd.lbl.gov/assets/pubs_presos/LBNL-729E.pdf

http://www.cql.com/

http://crd-legacy.lbl.gov/~kewu/fastbit/doc/html/

http://virtuoso.openlinksw.com/dataspace/doc/dav/wiki/Main/

http://www.luciddb.org/

http://www.monetdb.org/

http://infinidb.org/

http://skydb.io/

http://stackoverflow.com/questions/1052189/how-to-write-a-simple-database-engine

SQL GUI

http://www.postgresqlstudio.org

http://www.pgadmin.org/

http://torasql.com/

http://codegeekz.com/10-database-management-tools-for-developers/http://codegeekz.com/10-database-management-tools-for-developers/

http://www.ibm.com/developerworks/data/library/techarticle/dm-0312bhogal/

http://www.adminer.org/

Architecture

http://www.slideshare.net/mobile/pacoid/functional-programming-for-optimization-problems-in-big-data

http://logic.stanford.edu/dataintegration/

http://www.reddit.com/r/programming/comments/1kzw0c/mit_prof_michael_stonebraker_the_traditional/

http://engineering.linkedin.com/performance/optimizing-linux-memory-management-low-latency-high-throughput-databases

SQL compilation: native and just-in-time

http://www.scribd.com/doc/80559597/Interpreted-vs-Native-Compilation

http://technet.microsoft.com/en-us/library/dn249342(v=sql.120).aspx

http://mgogala.byethost5.com/Native_PLSQL_Execution.pdf

http://technet.microsoft.com/en-us/library/dn249342(v=sql.120).aspx

http://db.disi.unitn.eu/pages/VLDBProgram/pdf/tutorial/tut5.pdf Just-In-Time SQL compilation

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0005292.htm

http://technet.microsoft.com/en-gb/library/cc966425.aspx

http://sqlmag.com/t-sql/inside-sql-server-parse-compile-and-optimize

JDBC connections pooling

http://jolbox.com/

http://sourceforge.net/projects/proxool/

http://stackoverflow.com/questions/2835090/jdbc-connection-pooling

http://www.javaranch.com/journal/200601/JDBCConnectionPooling.html

http://static.springsource.com/projects/tc-server/6.0/admin/radmjdbc.html

http://blog.cloudera.com/blog/2013/02/inside-cloudera-impala-runtime-code-generation/

http://www.databasetube.com/database/big-data-lambda-architecture/

http://the-paper-trail.org/blog/columnar-storage/

http://www.somethingsimilar.com/2013/01/14/notes-on-distributed-systems-for-young-bloods/

http://www.slideshare.net/jboner/building-scalable-highly-concurrent-fault-tolerant-systems-lessons-learned

http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram

http://highscalability.com/all-time-favorites/

http://www.infoq.com/presentations/Storm-Introduction

http://www.dbms2.com/2013/01/05/newsql-thoughts/

http://metamarkets.com/category/technology/

http://metamarkets.com/category/technology/druid/

http://blog.programmableweb.com/2012/11/30/netflix-open-sources-resilience-engineering-code-library/

http://nathanmarz.com/blog/how-to-beat-the-cap-theorem.html

http://highscalability.com/blog/2012/11/26/bigdata-using-erlang-c-and-lisp-to-fight-the-tsunami-of-mobi.html

http://habrahabr.ru/company/teradata/blog/160821/

http://habrahabr.ru/hub/sql/

http://blogs.msdn.com/b/craigfr/archive/2008/05/15/query-processing-presentation.aspx

http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

The three possible join strategies are:

http://blog.kejser.org/2012/07/19/bigbig-table-joins/

- nested loop join: The right relation is scanned once for every row found in the left relation. This strategy is easy to implement but can be very time consuming. (However, if the right relation can be scanned with an index scan, this can be a good strategy. It is possible to use values from the current row of the left relation as keys for the index scan of the right.)
- merge sort join: Each relation is sorted on the join attributes before the join starts. Then the two relations are scanned in parallel, and matching rows are combined to form join rows. This kind of join is more attractive because each relation has to be scanned only once. The required sorting may be achieved either by an explicit sort step, or by scanning the relation in the proper order using an index on the join key.
- hash join: the right relation is first scanned and loaded into a hash table, using its join attributes as hash keys. Next the left relation is scanned and the appropriate values of every row found are used as hash keys to locate the matching rows in the table.

http://blogs.msdn.com/b/craigfr/archive/2006/09/20/hash-aggregate.aspx

Here is pseudo-code for the hash aggregate algorithm:

for each input row

begin

calculate hash value on group by column(s)

check for a matching row in the hash table

if we do not find a match

insert a new row into the hash table

else

update the matching row with the input row

end

output all rows in the hash table

Implementing DISTINCT

http://sqlblog.com/blogs/paul_white/archive/2011/12/04/is-distinct-aggregation-still-considered-harmful.aspx

Tabular Data Stream (TDS).

Redis

https://github.com/kondratovich/the-little-redis-book/blob/master/ru/redis.md

http://blog.seevl.fm/2013/11/22/simple-caching-with-redis/

http://www.slideshare.net/MinskPythonMeetup/ss-34235900

http://cramer.io/2014/05/12/scaling-sql-with-redis/

https://news.ycombinator.com/item?id=7736702

https://news.ycombinator.com/item?id=6780240

https://github.com/symisc/vedis

https://github.com/percolate/redset

Memcached

http://db-engines.com/en/system/Memcached%3BRedis

http://architects.dzone.com/articles/redis-memcached-replacement

http://stackoverflow.com/questions/10558465/memcache-vs-redis

http://mattturck.com/2012/10/11/10gen-mortar-datadog-rick-smolan-at-the-nyc-data-meetup/

http://metamarkets.com/2012/metamarkets-open-sources-druid/

http://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis/

http://symas.com/mdb/ LMDB

http://unqlite.org/ NoSQL embedded database

http://ejdb.org/ Embedded JSON Database

ETL tool: Talend

http://talendforge.org/exchange/index.php

http://www.talendbyexample.com/

InfiniSQL

http://highscalability.com/blog/2013/11/25/how-to-make-an-infinitely-scalable-relational-database-manag.html

DATALOG

http://www.learndatalogtoday.org/

DATOMIC

http://www.youtube.com/watch?v=Cym4TZwTCNU

http://blog.datomic.com/2013/10/datomic-console.html?m=1

http://www.infoq.com/presentations/datomic-functional-database

http://channel9.msdn.com/posts/Expert-to-Expert-Erik-Meijer-and-Rich-Hickey-Clojure-and-Datomic

http://www.dotkam.com/2013/05/31/datomic-can-simple-be-also-fast/

http://www.javacodegeeks.com/2013/06/making-sense-out-of-datomic-the-revolutionary-non-nosql-database.html

http://www.infoq.com/articles/Architecture-Datomic

http://www.infoq.com/interviews/hickey-datomic-cap

http://news.ycombinator.com/item?id=5093037

https://plus.google.com/communities/109115177403359845949

http://news.ycombinator.com/item?id=4733212 Comparison

http://habrahabr.ru/post/152477/

http://news.cs.nyu.edu/~jinyang/fa10/papers/franklin97concurrency.pdf

http://support.infobright.com/Support/Resource-Library/Whitepapers/

http://highlyscalable.wordpress.com/

http://dbmsmusings.blogspot.com/

http://robklopp.wordpress.com/

http://www.dbms2.com/

http://highlyscalable.wordpress.com/2012/09/18/distributed-algorithms-in-nosql-databases/

http://gigaom.com/cloud/is-stonebraker-right-why-sql-isnt-the-choice-du-jour-for-many-apps/

http://www.slideshare.net/nathanmarz/runaway-complexity-in-big-data-and-a-plan-to-stop-it

http://whitedb.org/

WhiteDB is a lightweight NoSQL database library written in C, operating fully in main memory. There is no server process. Data is read and written directly from/to shared memory, no sockets are used between WhiteDB and the application program.

rethinkdb.com

RethinkDB is built to store JSON documents, and scale to multiple machines with very little effort.

http://influxdb.org/

https://news.ycombinator.com/item?id=6676220

An open-source, distributed, time series, events, and metrics database

InfiniDB

http://www.calpont.com/overview

MemSQL

http://highscalability.com/blog/2012/8/14/memsql-architecture-the-fast-mvcc-inmem-lockfree-codegen-and.html

Virtuoso (supports RDF)

http://virtuoso.openlinksw.com/dataspace/doc/dav/wiki/Main/VOSIntro

http://dataddict.wordpress.com/2013/05/14/choosing-a-mpp-database-is-incredibly-hard/

Column-oriented databases

http://dbmsmusings.blogspot.com/2010/03/distinguishing-two-major-types-of_29.html

Aerospike

http://www.aerospike.com/

https://news.ycombinator.com/item?id=8093476

ArangoDB

https://www.youtube.com/watch?v=ASR7AX4bWjA&utm_source

http://www.slideshare.net/arangodb/introduction-to-column-oriented-databases

https://github.com/arangodb/arangodb

http://en.wikipedia.org/wiki/Column-oriented_DBMS

http://nms.csail.mit.edu/~stavros/pubs/tutorial2009-column_stores.pdf

http://www.vldb.org/pvldb/2/vldb09-tutorial6.pdf

http://rusanu.com/2012/05/29/inside-the-sql-server-2012-columnstore-indexes/

http://nms.csail.mit.edu/~stavros/pubs/tutorial2009-column_stores.pdf

http://www.b-eye-network.com/blogs/mcknight/archives/2010/04/understanding_c.php

http://dba.stackexchange.com/questions/10411/whats-the-anatomy-of-a-columnstore-index

Monetdb

http://www.monetdb.org/

http://sites.computer.org/debull/A12mar/monetdb.pdf

http://oai.cwi.nl/oai/asset/14832/14832A.pdf

SUPERSONIC

https://code.google.com/p/supersonic/

Vertica

http://en.wikipedia.org/wiki/Vertica

http://habrahabr.ru/post/206278/

https://habrahabr.ru/post/319902/

http://habrahabr.ru/post/190740/

http://habrahabr.ru/company/lifestreet/blog/146224/ Vertica/Analytics

http://habrahabr.ru/company/lifestreet/blog/147254/

http://habrahabr.ru/company/lifestreet/blog/200254/

http://ascrus.blogspot.com/2013/01/vertica-vs.html?showComment=1383245701324#c1068235135734199788

LMDB very fast

http://symas.com/mdb/

LevelDB

https://code.google.com/p/leveldb/

VEDIS

http://www.reddit.com/r/programming/comments/1mn8fo/vedis_an_embeddable_c_library_implementation_of/

http://unqlite.org/

NUODB

http://www.nuodb.com/

MDX

www.simba.com/docs/MDX-Myths-Facts-and-the-Future.pdf

http://blogs.simba.com/simba_technologies_ceo_co/2012/10/mdx-query-language-training-video.html

http://blogs.msdn.com/b/analysisservices/archive/2011/05/16/analysis-services-vision-amp-roadmap-update.aspx

http://www.bi-notes.com/2012/01/sas-olap-cubes-using-proc-sql-and-mdx-to-query-olap-cubes/

http://www.bp-msbi.com/2010/11/thoughts-on-bism-ssas-and-mdx/

http://siddhumehta.blogspot.com/2010/11/future-of-molap-cube-mdx-against-bism.html

DAX

http://www.purplefrogsystems.com/blog/2010/09/will-dax-replace-mdx/

http://cwebbbi.wordpress.com/2011/07/13/dax-queries-part-1/

Microsoft Azure

http://social.technet.microsoft.com/wiki/contents/articles/1695.inside-windows-azure-sql-database.aspx

http://habrahabr.ru/company/microsoft/blog/149505/

http://habrahabr.ru/company/microsoft/blog/149551/

NoSQL

http://pelle.github.com/Datomic/2012/07/08/thinking-in-datomic/

http://horicky.blogspot.nl/2012/07/couchbase-architecture.html CoachBase

https://cloudant.com/blog/cloudant-labs-on-google-spanner/ Spanner

http://citrusleaf.net/devIndex.php

http://highscalability.com/blog/2012/8/14/memsql-architecture-the-fast-mvcc-inmem-lockfree-codegen-and.html

http://openmymind.net/2011/3/28/The-Little-MongoDB-Book/

http://openmymind.net/2012/1/23/The-Little-Redis-Book/

Tools

http://code.google.com/p/wwwsqldesigner/ Visual DB Designer

https://github.com/andialbrecht/sqlparse/ Parsing SQL with Python

HyperLogLog http://antilamer.livejournal.com/431716.html

http://stackoverflow.com/questions/770273/database-internals-where-to-begin

http://stackoverflow.com/questions/838610/what-are-the-steps-followed-by-sql-engine-to-execute-the-query

http://highscalability.com/blog/2012/4/25/the-anatomy-of-search-technology-blekkos-nosql-database.html

TPCC benchmark

http://www.sqlservercentral.com/blogs/aschenbrenner/2011/11/23/running-a-tpc_2D00_c-workload-on-sql-server/

http://hammerora.sourceforge.net/

http://sourceforge.net/apps/mediawiki/osdldbt/index.php?title=Main_Page#Database_Test_2_.28DBT-2TM.29

http://tpccbench.codeplex.com/releases/view/34456

CQRS pattern

https://leanpub.com/cqrs BOOK

https://skillsmatter.com/skillscasts/1354-greg-young-cqrs-event-sourcing-the-business-perspective

http://www.reddit.com/r/programming/comments/2mvqzh/cqrs_a_stepbystep_guide_to_the_flow_of_a_typical/

http://danielwhittaker.me/2014/10/02/cqrs-step-step-guide-flow-typical-application/

http://habrahabr.ru/post/146429/#habracut

http://www.codeproject.com/Articles/555855/Introduction-to-CQRS

http://cqrs.files.wordpress.com/2010/11/cqrs_documents.pdf

http://martinfowler.com/bliki/CQRS.html

http://www.udidahan.com/2009/12/09/clarified-cqrs/

http://www.cqrsinfo.com/

http://elegantcode.com/2009/11/11/cqrs-la-greg-young/

http://msdn.microsoft.com/en-us/magazine/gg983487.aspx

Big Data http://alexmoskalyuk.livejournal.com/1202878.html

Advanced SQL

http://ebobby.org/2012/11/11/Using-Sets-For-Many-To-Many-Relationships.html

http://www.sqlexamples.info/sqladvanced.htm

http://www.optimaldba.com/papers/UKOUGSQLMasterClass.pdf

create table b(id number, bname VARCHAR2(10), constraint fk FOREIGN KEY (id) REFERENCES a(id));

insert into a values(1,'one');

insert into a values(2,'two');

commit;

insert into b(id) values(1);

insert into b(id) values(2);

commit;

update b set bname = (select a.name from a where a.id=b.id);

commit;

global temp tables

CREATE GLOBAL TEMPORARY TABLE temp_del (

column1 NUMBER,

column2 NUMBER

) ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE temp_preserve (

column1 NUMBER,

column2 NUMBER

) ON COMMIT PRESERVE ROWS;

Question: how to find all global tables?

To see number of rows without select count(*)

EXEC DBMS_STATS.GATHER_TABLE_STATS ('SCOTT','EMP');

select table_name, num_rows, global_stats from user_tables;

UPDATE table from another table

UPDATE <table_name> <alias>

SET (<column_name>,<column_name> ) = (

SELECT (<column_name>, <column_name>)

FROM <table_name>

WHERE <alias.column_name> = <alias.column_name>)

WHERE <column_name> <condition> <value>;

Update the results of a SELECT statement

UPDATE (<SELECT Statement>)

SET <column_name> = <value>

WHERE <column_name> <condition> <value>;

UPDATE ( SELECT * FROM test WHERE table_name NOT LIKE '%A%')

SET extent_management = 'Unknown' WHERE table_name NOT LIKE '%A%';

Pagination

http://use-the-index-luke.com/sql/partial-results/fetch-next-page

http://use-the-index-luke.com/sql/partial-results

SQL flavors

http://troels.arvin.dk/db/rdbms/

http://highscalability.com/

http://use-the-index-luke.com/sql/testing-scalability

http://habrahabr.ru/blogs/hi/

http://cacm.acm.org/magazines/2011/8/114953-an-overview-of-business-intelligence-technology/fulltext#

http://highscalability.com/blog/2011/6/20/35-use-cases-for-choosing-your-next-nosql-database.html

http://www.noupe.com/design/fantastic-information-architecture-resources.html

http://gigaom.com/cloud/

http://www.amazon.com/Beautiful-Architecture-Leading-Thinkers-Software/dp/059651798X

http://www.amazon.com/Beautiful-Data-Stories-Elegant-Solutions/dp/0596157118

http://www.aosabook.org/en/index.html Opes Source Architectures

http://www.amazon.com/IT-Architecture-reading-list/lm/RJVN7051L5T68/ref=cm_lmt_DYNA_f_1_russss0?pf_rd_p=496997231&pf_rd_s=listmania-center&pf_rd_t=201&pf_rd_i=0131401572&pf_rd_m=ATVPDKIKX0DER&pf_rd_r=1DBJHHWHDJYCQS3R2Z6F

http://horicky.blogspot.com/2010/10/scalable-system-design-patterns.html

http://www.cloudera.com/videos/doug-cutting-hadoop-platform-avro

http://radar.oreilly.com/2010/12/5-data-blogs-you-should-read.html

http://citforum.ru/database/advanced_intro/

In Memory Database

http://en.wikipedia.org/wiki/In-memory_database

http://docs.oracle.com/cd/E13085_01/doc/timesten.1121/e14261/arch.htm

http://www.theregister.co.uk/2012/03/12/oracle_exalytics_pricing_analysis/

Oracle TimesTen

http://www.oracle.com/technetwork/products/timesten/overview/index.html

http://www.oracle.com/us/products/database/timesten/overview/index.html

.NET ODBC

Practical Database Programming With Visual C#.NET Book

http://msdn.microsoft.com/en-us/library/system.data.odbc.odbcdatareader.aspx

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.Odbc; // ODBC definitions

namespace csMySQL {

class Program {

static void Main(string[] args) {

string strConnect = DSN=MySQL;UID=root;PWD=admin;DATABASE=store";

OdbcConnection dbMySQL = new OdbcConnection(strConnect);

try {

dbMySQL.Open();

OdbcCommand sqlCommand = dbMySQL.CreateCommand();

sqlCommand.CommandText ="select * from fruits order by cost";

OdbcDataReader sqlReader = sqlCommand.ExecuteReader();

// display result set column names

Console.WriteLine("{0,-12} {1}", sqlReader.GetName(0),

sqlReader.GetName(1));

Console.Write(("").PadRight(11, '-'));

Console.Write(" ");

Console.WriteLine(("").PadRight(11, '-'));

// display row values

while(sqlReader.Read()){

Console.WriteLine("{0,-12} {1}", sqlReader.GetString(0), sqlReader.GetString(1));

}

sqlReader.Close();

sqlCommand.Dispose();

dbMySQL.Close();

}

catch(OdbcException e) {

Console.WriteLine("Database Error\n\n{0}", e.ToString());

}

finally {

if(dbMySQL != null) dbMySQL.Close();

}

} // end of Main

} // end of Program

} //end of namespace csMySQL

ODBC

In ODBC 3.x, SQLExtendedFetch has been replaced by SQLFetchScroll. ODBC 3.x applications should not call SQLExtendedFetch; instead they should call SQLFetchScroll. The Driver Manager maps SQLFetchScroll to SQLExtendedFetch when working with an ODBC 2.xdriver. ODBC 3.x drivers should support SQLExtendedFetch if they want to work with ODBC 2.x applications that call it. For more information, see "Comments" and "Block Cursors, Scrollable Cursors, and Backward Compatibility" in Appendix G: Driver Guidelines for Backward Compatibility.

http://www.datadirect.com/resources/odbc/design2/required-data.html

http://stackoverflow.com/questions/7031870/how-can-i-portably-implement-row-wise-binding-of-arbitrary-resultsets-in-odbc-wh

http://www.codeproject.com/Articles/7621/Simple-ODBC-Wrapper

http://media.datadirect.com/download/docs/sequelnk/odbc30/rappc.htm

http://msdn.microsoft.com/en-us/library/ms709287(v=vs.85).aspx

http://www.easysoft.com/developer/languages/c/odbc_tutorial.html#odbc_ref

http://www.easysoft.com/developer/languages/c/odbc-tutorial-fetching-results.html

http://drdobbs.com/database/184416383

http://blogs.msdn.com/b/sqlblog/archive/2012/01/26/use-existing-msdn-c-odbc-samples-for-microsoft-linux-odbc-driver.aspx

http://simpledb.sourceforge.net/doc/index.html

http://code.google.com/p/tiodbc/

Bulk insert

http://objectmix.com/jdbc-java/41958-odbc-bulk-insert-using-column-binding.html

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.odbc%2Fsrc%2Ftpc%2Fdb2z_bulkinserts.htm

http://social.msdn.microsoft.com/Forums/en/sqldataaccess/thread/d494bea5-fda8-4763-99aa-e2a4f8c0fc79

http://msdn.microsoft.com/en-us/library/ms709287(v=vs.85).aspx

http://stackoverflow.com/questions/6525511/how-do-you-fetch-or-insert-rows-in-batches-using-odbc-in-c-or-c

http://msdn.microsoft.com/en-us/library/windows/desktop/ms709287(v=vs.85).aspx

http://msdn.microsoft.com/en-us/library/windows/desktop/ms711818(v=vs.85).aspx

SQL Server Internals

http://dba.stackexchange.com/questions/13851/where-to-start-learning-to-understand-sql-server-architecture-and-internals

http://social.technet.microsoft.com/wiki/contents/articles/sql-server-performance-survival-guide.aspx

http://www.altdevblogaday.com/2012/05/16/sql-server-high-performance-inserts/

http://blog.webagesolutions.com/archives/category/microsoft

Performance Testing

http://www.agiledata.org/essays/databaseTesting.html

http://code.google.com/p/quadrant-framework/

http://www.opensourcetesting.org/performance.php

http://www.qaguild.com/resources_tools.php#data

http://jakarta.apache.org/jmeter/

Wiki

http://en.wikipedia.org/wiki/List_of_wiki_software

http://en.wikipedia.org/wiki/Comparison_of_wiki_software

Public data Sets:

http://www.philwhln.com/how-to-get-experience-working-with-large-datasets

http://www.scidb.org/

SiSence

http://www.slideshare.net/cultureofperformance/sisense-overview

NoSQL

http://nosqltapes.com/

http://highlyscalable.wordpress.com/2012/03/01/nosql-data-modeling-techniques/

http://architects.dzone.com/articles/introduction-nosql-patterns

http://queue.acm.org/detail.cfm?id=1961297 READ IT!

http://habrahabr.ru/blogs/nosql/

http://www.vineetgupta.com/2010/01/nosql-databases-part-1-landscape/

http://www.dekorte.com/projects/opensource/vertexdb/

http://www.infinitegraph.com/

http://www.objectivity.com Sunnyvale

http://www.ingres.com/about/contact/worldwide-offices

http://www.infochimps.com/

http://kkovacs.eu/cassandra-vs-mongodb-vs-couchdb-vs-redis

http://blog.heroku.com/archives/2010/7/20/nosql/

http://abrdev.com/?p=1210

CouchDB: document-oriented schemless RESTFul JSON API is not for frequently changing data

Cassandra

Oracle

http://oracle.readthedocs.io/en/latest/index.html

http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/toc.htm

http://tahiti.oracle.com/

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/schema.htm/

http://www.oracle-base.com/articles/Articles.php

http://www.orafaq.com/wiki/Scripts

Oracle data dictionary

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/toc.htm

http://ss64.com/orad/

SQL> desc dict

TABLE_NAME VARCHAR2(30)

COMMENTS VARCHAR2(4000)

select * from b where bname like 'o%__' ESCAPE '_'; -- ESCAPE underscore

select * from b where bname like 'o_%%' ESCAPE '_'; -- ESCAPE underscore

$ORACLE_HOME/network/admin/ has 3 files:

tnsnames.ora file maps net service names to connect descriptors.

It provides the ability to reference oracle databases by a simple alias.

sqlnet.ora (on client machine) contains client side network configuration parameters.

listener.ora file consists of the listener configurations

ORACLE_SID

is a unique name for an Oracle database instance. The SID is included in the CONNECT DATA parts of the connect descriptors in a TNSNAMES.ORA file, and in the definition of the network listener in the LISTENER.ORA file. Also known as System ID.

SERVICE_NAME

Oracle Service Name may be anything descriptive like "MyOracleServiceORCL". In Windows, you can your Service Name running as a service under Windows Services.

The service name is used to identify a Oracle Net server process. The services file on client and server machines maps each service name to a port number and protocol.

GLOBAL_NAME

Global name is set up in your init.ora file. It can be changed later if you didn't set it up.

Global name is used for remote database links. If you don't set up a global name, your database will be known as ORACLE.WORLD . This will cause problems for people that remote link their databases into yours. So when setting up an Oracle instance, you should always set your global name to include your company's domain. Example, instead of saying oracledb for your global name, use oracledb.mycompany.com .

Even if you don't allow other companies to remote link to your databases, what if you install a second Oracle server? Both servers will think they're ORACLE.WORLD. So you might want to set up your global name to be something like oracledb1.mycompany.com , or, oracle-dev.mycompany.com and oracle-prod.mycompany.com ... or accounting.mycompany.com versus datawarehouse.mycompany.com .. etc etc

You can connect to 10g (or later) DBs which don't have a published tnsname using [//]host_name[:port][/service_name]

SQL*PLUS

http://www.orafaq.com/wiki/SQL*Plus_FAQ

http://ss64.com/ora/syntax-sqlplus.html

^H issue: if inside SQL*Plus the Backspace button produces ^H then type inside SQL*PLUS

!stty erase [BACKSPACE]

or put in your .profile or .bash_profile:

stty erase [CTRL-V,BACKSPACE]

Command line history and tab completion in SQL*PLUS

http://www.linuxification.at/rlwrap_ext.html.en

set feedback off

--set termout off

--set terminator on

WHENEVER SQLERROR exit oscode

WHENEVER OSERROR exit sql.sqlcode

var exitcode number

exec :exitcode := sql_function('&1')

exit :exitcode

write following code in shell script to receive the value

--call sql script

ret_val=$?

compose spool file name to include shema, db_name and date

col spoolname new_value spoolname

--select USER spoolname from dual;

select trim(user) || '_' || trim(v.instance_name) spoolname from dual, v$instance v;

spool '&spoolname..view.txt'

-- to include date:

col spoolname new_value spoolname

select 'invoice_'||to_char(sysdate, 'yymmdd') spoolname from dual

spool '&spoolname'

select stuff . . . .

spool off

find out all empty tables

set serveroutput on size unlimited

spool empty_tables_nettools_dba_approfprd.txt

DECLARE

cnt number;

n number :=0;

sql_stmt varchar2(100);

BEGIN

FOR rec in ( SELECT object_name from user_objects where object_type='TABLE' and object_name not like '%$%' order by 1)

LOOP

sql_stmt := 'SELECT COUNT(*) FROM ' || rec.object_name ||' WHERE ROWNUM < 2 ';

--DBMS_OUTPUT.PUT_LINE(sql_stmt);

execute immediate sql_stmt into cnt ;

n := n+1;

--EXIT when n >3;

IF cnt = 0 THEN

DBMS_OUTPUT.PUT_LINE(rec.object_name);

END IF;

END LOOP;

END;

spool off

Data dictionary

http://ss64.com/orad/

select name from v$database; select name from v$instance;

select * from global_name;

create test database

create user michael identified by a;

grant create session to michael;

grant all privileges to michael;

---

sqlplus michael/a

create table a(id number PRIMARY KEY, name VARCHAR2(10));

create table x3 (

id number ,

val number,

constraint pk_x3 primary key (id)

);

create table x4 (

id number,

val number,

constraint pk_x4 primary key (id)

);

insert into x3 values (1,55);

insert into x3 values (2,56);

insert into x4 values (1,57);

insert into x4 values (2,58);

-- this works fine:

update (

select x3.val, x4.val new_val

from x3 inner join x4 on x3.id=x4.id

) set val=new_val;

comments to Oracle objects

http://www.adp-gmbh.ch/ora/sql/comment.html

http://old.nabble.com/Displaying-Table-Comments-in-Oracle-td16943423.html

select distinct constraint_type from user_constraints;

Type Code Type Description Acts On Level

C Check on a table Column

O Read Only on a view Object

P Primary Key Object

R Referential(Foreign Key) Column

U Unique Key Column

V Check Option on a view Object

all the foreign key(referential integrity == R) constraints referencing the TEMP_JP1 table:

select owner,constraint_name,table_name,r_owner,r_constraint_name

from all_constraints

where constraint_type='R'

and r_constraint_name in (select constraint_name from all_constraints

where constraint_type in ('P','U') and table_name='TEMP_JP1');

Once you know the CONSTRAINT_NAME, you can use the USER_CONS_COLUMNS view to

find the COLUMN_NAME and POSITION for that CONSTRAINT_NAME

identify and disable/enable foreign key constraints on child tables:

select 'alter table '||a.owner||'.'||a.table_name|| ' disable constraint '||a.constraint_name||';'

from all_constraints a, all_constraints b

where a.constraint_type = 'R'

and a.r_constraint_name = b.constraint_name

and a.r_owner = b.owner

and b.table_name = 'TEMP_JP1';

relation between index and constraints

select b.table_name, b.index_name, a.constraint_type, b.index_type

from user_constraints a, user_indexes b where a.index_name=b.index_name;

SCHEMA REVERSE-ENGINEERING

DBA/ALL/USER_DEPENDENCIES (procedures,packages,functions,triggers, packages_bodies) but not tables

select name,type,owner from all_dependencies where referenced_owner='xxx' and referenced_name='SCOTT';

The general syntax of GET_DDL is

GET_DDL(object_type, name, schema, version, model, transform).

Parameters: version, model and transform above take the default values "COMPATIBLE", "ORACLE", and "DDL"

select DBMS_METADATA.GET_DDL('TABLE','EMPLOYEE')||'/' from dual;

SCRIPT TO EXTRACT ALL TABLES AND FOREIGN KEYS

set heading off

set pagesize 0

set long 100000

SET LONGCHUNKSIZE 10000

SET LINESIZE 1000

--set terminator on

execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);

exec dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',false);

exec dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);

exec dbms_metadata.set_transform_param(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',true);

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS_AS_ALTER',true);

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS',false);

spool tables.txt

SELECT DBMS_METADATA.GET_DDL(object_type,object_name)

FROM USER_OBJECTS

WHERE OBJECT_TYPE = 'TABLE' AND object_name NOT LIKE '%$%';

spool off

spool ref_constraints.txt

select dbms_metadata.get_ddl('REF_CONSTRAINT',CONSTRAINT_NAME) FROM USER_CONSTRAINTS

WHERE CONSTRAINT_TYPE='R' AND TABLE_NAME IN

(SELECT object_name FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND object_name NOT LIKE '%$%') ;

spool off

SCRIPT TO EXTRACT ALL VIEWS

set pagesize 0

set long 100000

SET LONGCHUNKSIZE 80000

SET LINESIZE 1000

--set terminator on

execute DBMS_METADATA.SET_TRANSFORM_PARAM

(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true)

SELECT DBMS_METADATA.GET_DDL(object_type,object_name)

FROM USER_OBJECTS WHERE OBJECT_TYPE = 'VIEW';

Object type can be: N -named; D- dependent; G-granted S-system

CONSTRAINT (Constraints) SND

DATABASE LINK (Database links) SN

DEFAULT_ROLE (Default roles) G

FUNCTION (Stored functions) SN

INDEX (Indexes) SND

MATERIALIZED_VIEW (Materialized views) SN

MATERIALIZED_VIEW_LOG (Materialized view logs) D

OBJECT_GRANT (Object grants) DG

PACKAGE (Stored packages) SN

PACKAGE SPEC (Package specifications) SN

PACKAGE_BODY (Package bodies) SN

PROCEDURE (Stored procedures) SN

ROLE (Roles) N

ROLE_GRANT (Role grants) G

SEQUENCE (Sequences) SN

SYNONYM (Synonyms) S

SYSTEM_GRANT (System privilege grants) G

TABLE (Tables) SN

TABLESPACE (Tablespaces) N

TRIGGER (Triggers) SND

TYPE (User-defined types) SN

TYPE_SPEC (Type specifications) SN

TYPE_BODY (Type bodies) SN

USER (Users) N

VIEW (Views) SN

DROP ALL USER OBJECTS

select 'drop '||object_type||' '|| object_name|| DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;','TYPE',' FORCE;',';')

from user_objects where 1=1 and object_type not IN

('TABLE PARTITION','TABLE SUBPARTITION','INDEX SUBPARTITION','INDEX PARTITION', 'INDEX')

order by object_type desc;

SELECT 'PURGE RECYCLEBIN;' FROM DUAL; #for 10G and above

SELECT COUNT(*) FROM USER_OBJECTS;

#another script to delete objects

set termout off

set feedback off

set pagesize 0

set linesize 200

col object_name format a30

col object_type format a30

col owner format a30

set trimspool on

spool dropall.sql

select 'DROP ' || object_type || ' '|| owner||'.'||object_name|| ';' fromall_objects where owner='&username';

spool off

@dropall.sql

PARTITIONING

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10739/partiti.htm

http://psoug.org/reference/partitions.html

Dictionary Views:

user_tab_partitions, user_part_tables, user_ind_partitions, user_part_indexes.

Self Referencing

SELECT INSTRUMENT_ID, NAME, SECTION_ID, LEVEL

FROM INSTRUMENT

START WITH INSTRUMENT_ID = 10

CONNECT BY PRIOR INSTRUMENT_ID = SECTION_ID;

CONNECT_BY_ISLEAF

CONNECT_BY_ROOT

CONNECT_BY_ISCYCLE will return 1 if a rowhas a child where that child row is also an ancestor of the row.

changing table ownership

http://www.pythian.com/news/218/oracle-how-to-move-a-table-to-another-schema/

http://www.freelists.org/post/oracle-l/Change-Schema-OWNER-of-ALL-Objects-in-10gR2,6

http://hell.cx/notes-oracle-change-users-tablespace.html

user login-logout auditing

http://hell.cx/notes-oracle-login-audit.html

Oracle ER-schema viewer

http://hyperschema.sourceforge.net/

http://www.oracle.com/technology/products/database/datamodeler/index.html

http://www.oracle.com/technology/products/database/sql_developer/index.html

http://www.sqlmanager.net/products/oracle/manager

A SELECT statement can contain a CURSOR expression:

SELECT CY.NAME, CURSOR

(SELECT NAME FROM CONTINENT WHERE CONTINENT_ID = CY.CONTINENT_ID)

FROM COUNTRY CY;

Multitables insert:

INSERT FIRST

WHEN RETAILER_ID = (SELECT ID FROM RETAILER WHERE NAME = 'Amazon') THEN INTO AMAZON

WHEN RETAILER_ID = (SELECT ID FROM RETAILER WHERE NAME = 'Barnes') THEN INTO BANDN

WHEN RETAILER_ID = (SELECT ID FROM RETAILER WHERE NAME = 'CD Shop') THEN INTO CDSHOP

SELECT * FROM SALES;

Merge

MERGE INTO HISTORY_STUDIOTIME HS

USING STUDIOTIME S ON (S.STUDIOTIME_ID = HS.STUDIOTIME_ID)

WHEN MATCHED THEN UPDATE

SET DUE_DATE = S.DUE_DATE, AMOUNT_PAID = S.AMOUNT_PAID

WHEN NOT MATCHED THEN INSERT VALUES

(S.STUDIOTIME_ID, S.ARTIST_ID, S.SESSION_DATE, S.DUE_DATE, S.AMOUNT_PAID);

REF Cursor

http://www.oradev.com/ref_cursor.jsp

With the REF_CURSOR you can return a recordset/cursor from a stored procedure.

There are 2 basic types: Strong ref cursor and weak ref cursor

For the strong ref cursor the returning columns with datatype and length need to be known at compile time.

For the weak ref cursor the structure does not need to be known at compile time.

The STRONG_REF_CURSOR and until Oracle 9i also the weak-type need to be declared in a package structure lik this:

create or replace package REFCURSOR_PKG as

TYPE WEAK8i_REF_CURSOR IS REF CURSOR;

TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;

end REFCURSOR_PKG;

The pl/sql procedure that returns a ref-cursor looks like this:

/** until Oracle 9 */

create or replace procedure test( p_deptno IN number,

p_cursor OUT REFCURSOR_PKG.WEAK8i_REF_CURSOR)

begin

open p_cursor FOR select * from emp where deptno = p_deptno;

end test;

--------------

Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning REF_CURSOR.

/** From Oracle 9 */

create or replace procedure test( p_deptno IN number, p_cursor OUT SYS_REFCURSOR)

begin

open p_cursor FOR select * from emp where deptno = p_deptno;

end test;

/* Strong type */

create or replace procedure test( p_deptno IN number

, p_cursor OUT REFCURSOR_PKG.STRONG REF_CURSOR)

begin

open p_cursor FOR

select * from emp where deptno = p_deptno;

end test;

---------------

Bulk Collect http://psoug.org/reference/array_processing.html

1 Declare

2 TYPE TYP_TAB_EMP IS TABLE OF EMP.EMPNO%Type ;

3 Temp_no TYP_TAB_EMP ; -- collection of EMP.EMPNO%Type

4 Cursor C_EMP is Select empno From EMP ;

5 Pass Pls_integer := 1 ;

6 Begin

7 Open C_EMP ;

8 Loop

9 -- Fetch the table 3 by 3 --

10 Fetch C_EMP BULK COLLECT into Temp_no LIMIT 3 ;

11 Exit When C_EMP%NOTFOUND ;

12 For i In Temp_no.first..Temp_no.last Loop

13 dbms_output.put_line( 'Pass ' || to_char(Pass) || ' Empno= ' || Temp_no(i) ) ;

14 End loop ;

15 Pass := Pass + 1 ;

16 End Loop ;

17 End ;

18 /

Index-organizwd tables: all columns in the table are stored as a BTree index, based on the primary key

Cluster tables

External table

Partitional table

Types of Partitions

There are five different types of partitions as follows:

- Range. Divides up rows based on ranges of values.

- Values List. Divides up rows based on sets of literal values.

- Hash. Uses a hashing algorithm to divide rows, resulting in the most consistently sized partitions.

* Composite Partitions. Contains sub-partitions within each separate partition:

- Range-Hash. A range partition containing hash subpartitions within each range partition.

- Range-List. A range partition containing list value subpartitionswithin each range partition.

CREATE TABLE SALESLIST PARTITION BY LIST (CONTINENT_ID)(

PARTITION EuropeAndAmerica VALUES (1,2,3,4)

,PARTITION EverywhereElse VALUES (5,6,7,8,9,10))

AS SELECT * FROM SALES;

Pseudocolumns:

ROWID, ROWNUM Sequence.CURRVAL.NEXTVAL,

LEVEL ( used in CONNECTED BY)

CONNECTED BY {IS[LEAF|CYCLE]}

VERSIONS...

OBJECT_ID, OBJECT_VALUE

---------------------------

UNION ALL. Retrieves all rows from both queries including duplicates.

UNION duplicate rows are removed.

INTERSECT

MINUS

----------------------------------------------------------

Redo log entries are always written before any database changes to

ensure recoverability

MERGE.

CUBE and ROLLUP

GROUP BY extensions and the RANK function

A query of data at a point in time in the past (called a Flashback

query) is enhanced for use within an SQL command rather than

requiring an environmental session change.

Much of the memory structure or buffers is known collectively as

the Shared Global Area (SGA). The SGA contains database buffer

caches, the shared pool, and the redo log buffer.

http://www.gplivna.eu/papers_e.htm

http://highscalability.com/

http://sqlzoo.net/

http://community.voltdb.com/

Development Tools

http://www.google.com/Top/Computers/Programming/Languages/PL-SQL/Developer_Tools/

http://www.aquafold.com/index.html

http://sqldeveloper.solyp.com/index.html

http://www.sqlpower.ca/page/architect

http://www.dbvis.com/

Free:

http://www.sqldeveloper.net/database-tools/products/download-center.html

http://schemacrawler.sourceforge.net/

http://squirrel-sql.sourceforge.net/

http://schemaspy.sourceforge.net/

http://www.keeptool.com/de/download.php

Page updated

Google Sites

Report abuse