Logo Host-telecom.com — профессиональный хостинг в Европе! Море(!) аналитической информации!
IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware
хостинг сайтов ГиперХост — хостинг сайтов который Вы искали.

Виртуальный хостинг, Аренда VPS серверов, рация доменных имен, SSL сертификаты

💰 Самые низкие цены на домены

🔒 Отличный хостинг на SSD c бесплатными SSL

💻 Огромнейший выбор dedicated выделенных серверов

Виртуальные VPS серверы в РФ и ЕС

Dedicated серверы в РФ и ЕС

По промокоду CITFORUM скидка 30% на заказ VPS\VDS

VDS хостинг Облачный сервер в Нидерландах и Украине

Аренда виртуального сервера от $7.91

Партнёрская программа
$20 за клиента

2008 г.

СУБД с хранением данных по столбцами и по строкам: насколько они отличаются в действительности?

Дэниэль Абади, Сэмюэль Мэдден, Набил Хачем
Пересказ: Сергей Кузнецов

Назад Оглавление

Приложение 1. Запросы тестового набора SSBM

Звено 1

Запрос 1.1
select sum(lo_extendedprice*lo_discount) as revenue
from lineorder, date 
where lo_orderdate = d_datekey
  and d_year = 1993
  and lo_discount betweenl
  and 3 and lo_quantity < 25;
Запрос 1.2
select sum(lo_extendedprice*lo_discount) as revenue
from lineorder, date 
where lo_orderdate = d_datekey
  and d_yearmonth = 199401
  and lo_discount between 4 and 6
  and lo_quantity between 26 and 35; 
Запрос 1.3
select sum(lo_extendedprice*lo_discount) as revenue
from lineorder, date 
where lo_orderdate = d_datekey
  and d_weeknuminyear = 6
  and d_year = 1994
  and lo_discount between 5 and 7
  and lo_quantity between 26 and 35; 

Звено 2

Запрос 2.1
select sum(lo_revenue), d_year, p_brandl
from lineorder, date, part, supplier
where lo_orderdate = d_datekey
  and lo_partkey = p_partkey
  and lo_suppkey = s_suppkey
  and p_category = ‘MFGR#12’
  and s_region = ‘AMERICA’
group by d_year, p_brandl
order by d_year, p_brandl; 
Запрос 2.2
select sum(lo_revenue), d_year, p_brandl
from lineorder, date, part, supplier
where lo_orderdate = d_datekey
  and lo_partkey = p_partkey
  and lo_suppkey = s_suppkey
  and p_brandl between ‘MFGR#2221’ and ‘MFGR#2228’
  and s_region = ‘ASIA’
group by d_year, p_brandl
order by d_year, p_brandl; 
Запрос 2.3
select sum(lo_revenue), d_year, p_brandl
from lineorder, date, part, supplier
where lo_orderdate = d_datekey
  and lo_partkey = p_partkey
  and lo_suppkey = s_suppkey
  and p_brandl= ‘MFGR#2239’
  and s_region = ‘EUROPE’
group by d_year, p_brandl
order by d_year, p_brandl; 

Звено 3

Запрос 3.1
select c_nation, s_nation, d_year, sum(lo_revenue) as revenue
from customer, lineorder, supplier, date 
where lo_custkey = c_custkey
  and lo_suppkey = s_suppkey
  and lo_orderdate = d_datekey
  and c_region = ‘ASIA’
  and s_region = ‘ASIA’
  and d_year >= 1992 and d_year <= 1997
group by c_nation, s_nation, d_year 
order by d_year asc, revenue desc; 
Запрос 3.2
select c_city, s_city, d_year, sum(lo_revenue) as revenue
from customer, lineorder, supplier, date 
where lo_custkey = c_custkey 
  and lo_suppkey = s_suppkey 
  and lo_orderdate = d_datekey 
  and c_nation = ‘UNITED STATES’
  and s_nation = ‘UNITED STATES’
  and d_year >= 1992 and d_year <= 1997
group by c_city, s_city, d_year
order by d_year asc, revenue desc; 
Запрос 3.3
select c_city, s_city, d_year, sum(lo_revenue) as revenue
from customer, lineorder, supplier, date
where lo_custkey = c_custkey
  and lo_suppkey = s_suppkey
  and lo_orderdate = d_datekey
  and (c_city = ‘UNITED КI1’ or c_city = ‘UNlTED KI5’)
  and (s_city = ‘UNlTED KI1’ or s_city = ‘UNITED KI5’) 
  and d_year >= 1992 and d_year <= 1997 
group by c_city, s_city, d_year 
order by d_year asc, revenue desc; 
Запрос 3.4
select c_city, s_city, d_year, sum(lo_revenue) as revenue
from customer, lineorder, supplier, date 
where lo_custkey = c_custkey 
  and lo_suppkey = s_suppkey 
  and lo_orderdate = d_datekey
  and (c_city = ‘UNITED КI1’ or c_city = ‘UNlTED KI5’) 
  and (s_city = ‘UNlTED KI1’ or s_city = ‘UNlTED KI5’) 
  and d_yearmonth = ‘Decl997’ 
group by c_city, s_city, d_year 
order by d_year asc, revenue desc; 

Звено 4

Запрос 4.1
select d_year, c_nation, 
       sum(lo_revenue - lo_supplycost) as profit 
from date, customer, supplier, part, lineorder 
where lo_custkey = c_custkey 
  and lo_suppkey = s_suppkey 
  and lo_partkey = p_partkey 
  and lo_orderdate = d_datekey 
  and c_region = ‘AMERICA’ 
  and s_region = ‘AMERICA’ 
  and (p_mfgr = ‘MFGR#1’ or p_mfgr = ‘MFGR#2’) 
group by d_year, c_nation 
order by d_year, c_nation; 
Запрос 4.2
select d_year, s_nation, p_category, 
       sum(lo_revenue - lo_supplycost) as profit 
from date, customer, supplier, part, lineorder 
where lo_custkey = c_custkey 
and lo_suppkey = s_suppkey 
and lo_partkey = p_partkey 
and lo_orderdate = d_datekey 
and c_region = ‘AMERICA’ 
and s_region = ‘AMERICA’ 
and (d_year = 1997 or d_year = 1998) 
and (p_mfgr = ‘MFGR#1’ or p_mfgr = ‘MFGR#2’) 
group by d_year, s_nation, p_category 
order by d_year, s_nation, p_category; 
Запрос 4.3
select d_year, s_city, p_brandl, 
       sum(lo_revenue - lo_supplycost) as profit 
from date, customer, supplier, part, lineorder 
where lo_custkey = c_custkey 
  and lo_suppkey = s_suppkey 
  and lo_partkey = p_partkey 
  and lo_orderdate = d_datekey 
  and s_nation = ‘UNITED STATES’ 
  and (d_year = 1997 or d_year = 1998) 
  and p_category = ‘MFGR#14’ 
group by d_year, s_city, p_brandl 
order by d_year, s_city, p_brandl; 

Приложение 2. Определение таблиц базы данных SSBM

LINEORDER Table Layout (SF*6,000,000 are populated) 
ORDERKEY numeric (int up to SF 300) first 8 of each 32 keys used 
LINENUMBER numeric 1-7
CUSTKEY numeric identifier foreign key reference to C_CUSTKEY 
PARTKEY identifier foreign key reference to P_PARTKEY 
SUPPKEY numeric identifier foreign key reference to S_SUPPKEY 
ORDERDATE identifier foreign key reference to D_DATEKEY 
ORDERPRIORITY fixed text, size 15 (5 Priorities: 1-URGENT, etc.) 
SHIPPRIORITY fixed text, size 1 
QUANTITY numeric 1-50 (for PART) 
EXTENDEDPRICE numeric, MAX about 55,450 (for PART) 
ORDTOTALPRICE numeric, MAX about 388,000 (for ORDER) 
DISCOUNT numeric 0-10 (for PART) - (Represents PERCENT) 
REVENUE numeric (for PART: (extendedprice*(100-discount))/100) 
SUPPLYCOST numeric (for PART, cost from supplier, max = ?) 
TAX numeric 0-8 (for PART)
COMMITDATE Foreign Key reference to D_DATEKEY 
SHIPMODE fixed text, size 10 (Modes: REG AIR, AIR, etc.) 
Compound Primary Key: ORDERKEY, LINENUMBER

PART Table Layout (200,000*[l+log2SF] populated)
PARTKEY identifier
NAME variable text, size 22 (Not unique per PART but never was)
MFGR fixed text, size 6 (MFGR#l-5, CARD = 5)
CATEGORY fixed text, size 7 (‘MFGR#’||l-5||l-5: CARD = 25)
BRAND1 fixed text, size 9 (CATEGORY||1-40: CARD = 1000)
COLOR variable text, size 11 (CARD = 94)
TYPE variable text, size 25 (CARD = 150)
SIZE numeric 1-50 (CARD = 50)
CONTAINER fixed text(10) (CARD = 40)

Primary Key: PARTKEY SUPPLIER Table Layout (SF*10,000 are populated) SUPPKEY identifier NAME fixed text, size 25: ‘Supplier’||SUPPKEY ADDRESS variable text, size 25 (city below) CITY fixed text, size 10 (10/nation: nation_prefix||(0-9)) NATION fixed text(15) (25 values, longest UNITED KINGDOM) REGION fixed text, size 12 (5 values: longest MIDDLE EAST) PHONE fixed text, size 15 (many values, format: 43-617-354-1222) Primary Key: SUPPKEY CUSTOMER Table Layout (SF*30,000 are populated) CUSTKEY numeric identifier NAME variable text, size 25 ‘Customer’||CUSTKEY ADDRESS variable text, size 25 (city below) CITY fixed text, size 10 (10/nation: NATION_PREFIX||(0-9) NATION fixed text(15) (25 values, longest UNITED KINGDOM) REGION fixed text, size 12 (5 values: longest MIDDLE EAST) PHONE fixed text, size 15 (many values, format: 43-617-354-1222) MKTSEGMENT fixed text, size 10 (longest is AUTOMOBILE) Primary Key: CUSTKEY DATE Table Layout (7 years of days: 7366 days) DATEKEY identifier, unique id - e.g. 19980327 (what we use) DATE fixed text, size 18, longest: December 22, 1998 DAYOFWEEK fixed text, size 8, Sunday, Monday Saturday) MONTH fixed text, size 9: January, …, December YEAR unique value 1992-1998 YEARMONTHNUM numeric (YYYYMM) - e.g. 199803 YEARMONTH fixed text, size 7: Marl998 for example DAYNUMINWEEK numeric 1-7 DAYNUMINMONTH numeric 1-31 DAYNUMINYEAR numeric 1-366 MONTHNUMINYEAR numeric 1-12 WEEKNUMINYEAR numeric 1-53 SELLINGSEASON text, size 12 (Christmas, Summer,...) LASTDAYINWEEKFL 1 bit LASTDAYINMONTHFL 1 bit HOLIDAYFL 1 bit WEEKDAYFL 1 bit Primary Key: DATEKEY

Назад Оглавление

Wildcard сертификаты от $74,97 в год.

Дешевые ssl сертификаты для домена

Sectigo сертификаты от $7,67 в год.

хостинг Украина Виртуальный хостинг для сайта от $4,87

Регистрация домена от $2 в год

Партнерская программа – $20 за клиента

VPS с гибкой конфигурацией: за 1€

Мощные выделенные сервера: от 25€

Собственный Дата-Центр
Поддержка 24/7

Бесплатный конструктор сайтов и Landing Page

Хостинг с DDoS защитой от 2.5$ + Бесплатный SSL и Домен

SSD VPS в Нидерландах под различные задачи от 2.6$

ATLEX Выделенные серверы: в Европе / в России.

Виртуальные серверы: в Европе / в России.

Партнерская программа

Ваш сайт в 8 раз быстрее конкурентов. Хостинг от $2.95

VPS: SSD, KVM, бесплатные бэкапы и администрирование

Все необходимое для вашего сайта и лучшая техподдержка 24/7

Новости мира IT:

Архив новостей

Последние комментарии:

IT-консалтинг Software Engineering Программирование СУБД Безопасность Internet Сети Операционные системы Hardware

Информация для рекламодателей PR-акции, размещение рекламы — adv@citforum.ru,
тел. +7 495 7861149
Пресс-релизы — pr@citforum.ru
Обратная связь
Информация для авторов
Rambler's Top100 TopList liveinternet.ru: показано число просмотров за 24 часа, посетителей за 24 часа и за сегодня This Web server launched on February 24, 1997
Copyright © 1997-2000 CIT, © 2001-2019 CIT Forum
Внимание! Любой из материалов, опубликованных на этом сервере, не может быть воспроизведен в какой бы то ни было форме и какими бы то ни было средствами без письменного разрешения владельцев авторских прав. Подробнее...